Example of Calculating Variance in Excel
Follow the below steps to calculate variance:
Step 1: Organize your data
There are a few options for the formula to calculate variance depending on the type of data and the desired outcome. : Calculate the Mean( Average)
=AVERAGE(range)
Replace “range” with the actual range of data values. Press enter to obtain the mean value.
Step 3: Calculate the Deviation.
Find the Deviation of each data point from the mean. The deviation is obtained by subtracting the mean from each data point. (A2- $B$2)
Step 4: Square the Deviations
Now Square each deviation to prepare for calculating variance.
Step 5: Calculate Variance
Step 6: Interpret the variance
Let’s see the difference here,
Here, the discrepancy occurs because VARPA and VARA read TRUE as 1 and VAR.P and VAR.S ignore it.
The formula goes like this –
- =STDEV.P(A38:A43) [Formula used in A37 cell]
- =STDEV.S(B38:B43) [Formula used in B37 cell]
- =STDEVPA(C38:B43) [Formula used in C37 cell]
- =STDEVA(D38:B43) [Formula used in D37 cell]
Note: VAR and VARP are for Excel 2007 or earlier. DVAR and DVARP are used for fetching data from Database.
How To Calculate Variance in Excel
If you’re looking to understand your data better, knowing how to calculate variance in Excel is a great skill to have. Calculating Variance in Excel is a Fundamental calculation that helps to measure the dispersion or spread of data points in a dataset. Luckily, Excel makes it easy to do this with some handy formulas.
In this article, we’ll walk you through how to use Excel to calculate variance, step by step. Whether you’re new to Excel or just need a quick refresher on variance, we’ve got you covered.
How To Calculate Variance in Excel