How to Calculate Variance on Excel
Excel 2013 quickly calculates variance of sample or population data without needing to first calculate a mean or sum of squared differences.
Variance measures the amount of variability in data by factoring in the number of data points and each point's deviation from the mean. High variance indicates that data varied widely from the calculated average, or that there weren't enough data points to know if the mean is closely suggestive of future measurements.
As an example, if sales figures were exactly $150 each day of the year, the average would be $150 and the zero variation gives confidence that upcoming sales will also be close to $150. However, if the figures ranged from zero to $300, the average might still be $150, but the high variance tells you upcoming sales could fall into a large range of possible results. Likewise, if you only measured two days of sales --say $130 in one and $170 in the other -- the average is the same, but the low number of measurements illustrates lower confidence in the mean.
Excel 2013 integrates six functions to calculate variance of your data. In most cases, you probably only need the Var.S or Var.P functions, depending on if your data is a sample or the entire population, respectively.
Determine if your data is a sample or a population. A sample is a subset of a larger population of data, such as measuring daily sales data over a certain period of time to help predict future sales. However, if you were measuring sales every day of the year to produce an average and accompanying variance for only that year, that would be a population, because you're including every possible data point for the intended measurement. That is, you're not inferring future values and are instead measuring the entirety of data. As you might expect, sample variance tends to be larger than population variance, because there's more uncertainty due to the uncollected data points.
Enter the data points in an Excel spreadsheet. As an example, you might enter total daily sales in cells A1 through A60.
You don't have to enter data in a single column. Data can span multiple columns or even multiple sheets, as long as you reference them appropriately in the formula.
Use the VAR.S or VAR.P function to calculate sample or population variance, respectively, using the format =VAR.S(range) or =VAR.P(range) where "range" references the previously entered data. If the example was being used as a sample, enter =VAR.S(A1:A60). Alternatively, enter =VAR.S(A:A) if no other data are included in the column.
- To reference multiple columns, enter the top-left cell and the bottom-right cell, separated by a colon, such as =VAR.S(A1:C60).
- To reference another sheet, enter the sheet name, followed by an exclamation point and then the referenced cells, such as =VAR.S(Sheet1!A:A).
- To reference discontinuous cells, separate references with a comma, such as =VAR.S(Sheet1!A:A,B1:B60).
- To directly enter figures, separate them with a comma, such as =VAR.S(170,130).
- The VAR and VARP functions are still usable in Excel 2013 for compatibility with spreadsheets created in older versions of Excel. However, these functions may be deprecated in future versions. To future-proof your work, use VAR.S or VAR.P instead, which serve the same function as VAR and VARP, respectively.
- Excel 2013 also includes VARA and VARPA functions that are similar to VAR.S and VAR.P. The only differences are how they handle text values or logical values in arrays. VARA and VARPA count text, including empty text or spaces, as zeros which potentially affect the variance calculation. Unless you have a specific reason to use VARA or VARPA, use VAR.S or VAR.P.