In this blog post, we will show you how to calculate variance in Excel.
Variance is a measure of how spread out a set of data points are.
It can be used to identify whether a data set is symmetric or not.
In addition, it can also be used to measure the risk of an investment.
We will show you how to calculate variance both with and without the use of Excel functions.
Let’s get started!
Table of Contents
How to Calculate Variance in Excel
Suppose this is the dataset you will use for both sample and population variance computation.
Method 1. Calculating Sample Variance
Step 1. Open your Excel workbook
Step 2. Select the cell where you want the sample variance to appear
The cell you click should appear with a dark green border.
Step 3. Locate the cell range of your data
Identify the cell range of your data by checking the cell coordinates, composed of a column letter and row number.
A cell range is then the first and last cell coordinates separated by a colon.
Thus, the cell range of your needed data is A2:A11.
Step 4. Input the VAR formula
Since you are only going to calculate the sample variance of the dataset, the function to be utilized is VAR.
Input the following formula into the formula bar:
=VAR(A2:A11)
Step 5: Click “Enter” on your keyboard
Clicking enter should automatically add the sample variance into the cell you selected in step 2.
Method 2. Calculating Population Variance
The steps mentioned in the first method will also be used to compute population variance.
The only difference is the formula you have to input.
Step 1. Load up your sheet
Step 2. Select the cell where you want the population variance to appear
The selected cell should appear with a dark green border.
Step 3. Locate the cell range of your data
Identify the cell range of your data by checking the column letter and row number.
It is the location of the first and last cells separated by a colon.
The cell range of the data in this set is A2:A11.
Step 4. Input the VARP formula
Unlike the first method, which only uses the VAR function, this method utilizes VARP since it computes the entire data population.
Thus, input the following formula into the formula bar:
=VARP(A2:A11)
Step 5: Click “Enter” on your keyboard
Clicking enter should automatically add the population variance into the cell you selected in step 2.
Conclusion
Calculating the variance of datasets becomes a simple task through the use of spreadsheet applications like Google Sheets and Excel.
These applications utilize specific functions that automatically calculate the data inside a range of cells once you input the appropriate formula.
This article breaks down the steps to calculate two types of variances: sample and population variance in Google Sheets and Excel.
Summary
That’s the end of this tutorial.
We hope this article helps you learn how to calculate Variance in Excel