How To Calculate Variance In Google Sheets

In this tutorial, you will learn how to calculate variance in google sheets.

If you have a sample of data in Google Sheets, you may want to calculate the Variance of that sample to get insights into the distribution. The variance of a sample measures the degree to which the average datapoint in the set differs from the mean. It can be useful for statistical analysis in and of itself, but is also a step in calculating standard deviation. Read on to learn how to calculate variance in Google Sheets.

Calculate Variance of a Sample

In most cases, the data in your spreadsheet will represent a sample, not the entire population, so you’ll want to use the VAR() function, which calculates variance for a sample. Here’s how:

Step 1

Enter your sample data in a single column in Google Sheets

Step 2

Select a cell to calculate variance in and type the following formula: “=VAR(A2:A21)”, where the range A2:A21 contains the sample data

Step 3

Hit enter and the variance will calculate. By default, Google Sheets will show eight decimal places, but you can reduce this by clicking on the Decrease Decimal Places button in the toolbar as many times as desired

Calculate Variance of an Entire Population

Google Sheets also has a function for calculating population variance, which is calculated slightly differently. In general, you should use the sample variance calculation whenever your dataset represents only a sample of the larger population you’re interested in. Use the population variance calculation only in cases where your data represents all relevant data points of the entire population.

Population variance can be calculated following the same steps as sample variance, simply use the VARP() function instead of VAR(). The P stands for Population. So for population data stored in the range A2:A21, the formula is: “=VARP(A2:A21)


Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to calculate variance in google sheets. Want more? Check out all the Google Sheets Tutorials.