How to Calculate Variance in Excel

-

|

Introduction

Variance measures the spread between numbers in a data set. You can calculate variance in Excel using the VAR, VAR.S, VARP or VAR.P.

In this guide you will learn how to calculate sample variance in excel using the VAR.S. The guide also covers how to calculate the variance of an entire data set using VAR.P.

In the last section of the guide I will show how to calculate variance in Excel from Mean.

VAR.S and VAR.P are the newer versions of the VAR and VARP functions. For this guide, VAR.S and VAR.P functions will be used in the demo.
Advertisement

How to Calculate Variance in Excel (Sample of a Population)

The image below column A contains a data set.

To calculate variance in Excel using the sample data shown above, use the VAR.S function.

The syntax of VAR.S function is

VAR.S(number1,[number2],...) 

Number1 (required) – represents the first number in a sample of a population
Number2 (optional) – represents a number between 2 to 254 in a sample of a population

Important Notes

  1. Use the VAR.S function if your data set represents a sample of the population. If your data represents the entire population, use VAR.P to compute your variance instead.
  2. Number1, Number2 etc can either be numbers or names, arrays, or references that contain numbers.
  3. If the input of VAR.S is an array or reference, only numbers in that array or reference are counted.
  4. To include logical values and text representations of numbers in a reference to your calculation, use the VARA function.
  5. VAR.S uses the formula below:
Excel Variance Formula
from Microsoft.com

In the formula above, x is the Mean of the sample. The Mean is calculated using AVERAGE(number1,number2,…). n is the sample size.

Later in this guide I will use the formula in no 5 above to calculate variance from Mean.

Using the data shown in the previous image, to calculate the variance, use the formula below:

=VAR.S(A2:A11)

Here is the result in Excel

How to Calculate sample Variance in Excel with VAR.S function

How to Calculate Variance in Excel (Entire Population)

In the last section I showed how to calculate variance using sample of a data set. If you want to use the entire population you need to use the VAR.P function.

The syntax of the VAR.P is similar to VAR.S.

To calculate the variance of the entire population shown in the previous image, use this formula:

=VARP(A2:A11)

The result is shown below:

How to Calculate Variance in Excel with VAR.P function

As you can see from the result, the result is different from the the result for VAR.S function.

How to Calculate Variance in Excel from Mean

So far I have shown how to calculate variance in Excel using the VAR.S and VAR.P functions.

In this section, I will show how to calculate variance in Excel using the Mean of a sample population.

The worksheet in the image below will be referenced in this demo.

calculate-variance-from-Mean
Advertisement

The next steps show how to use the formula below to calculate variance:

Excel Variance Formula

Here are the steps:

  • Calculate the Mean (Average) of the sample using the formula:
=AVERAGE(A2:A11)

The result is shown in cell C13 (See the image above).

  • Next, calculate the dispersion of each of the sample from their Mean. To do this, subtract each sample data from the Mean. The results are in cells B2 to B11.
  • Then square the dispersion from the Mean. Cells C2 to C11 contains the squared values.
  • Next, sum the squared values. The result of the sum is 6788.4. See cell C14.
  • The next step is to count the total sample and subtract 1 from it. Here is the formula used (C15).
=COUNT(A2:A11)-1
  • Finally, to calculate the variance divide C14 by C15. The formula is in cell C16
=C14/C15

The result is 754.2667. The same value we got with VAR.S function.

This last section will be useful for college students. If you are required to calculate variance but show the steps, use the steps in this section.

Conclusion

Variance calculation using Excel is very straight forward. In this guide I showed various ways you can calculate variance in Excel.

In the last section you saw how to perform variance calculation from the Mean of a sample. Use this if you need to show steps to calculate variance.

I hope this guide has been helpful. Have any question or comment? Use the “Leave a Reply” form at the end of this page.

Other Helpful Guides

Additional Resources and References

LEAVE A REPLY

Please enter your comment!
Please enter your name here

FEATURED POSTS

network discovery keeps turning off server 2016

How to Fix Network Discovery If it Keeps Turning Off in Server 2016

Does network discovery keep turning off in your Windows server 2016? It is likely that one of its dependent services is not...
how to install windows 10 1903 update manually

How to Install Windows 10 1903 Update Manually

Windows 10 1903 Update was released in May, 2019. But some users are not yet offered the update via automatic update. The...
spotify web player not working

Spotify Web Player Not Working [Fixed]

Introduction Spotify Web Player may stop working for you with the following error messages: "Spotify Web Player an Error...
DISM.exe /Online /Cleanup-Image /Restorehealth

DISM.exe /Online /Cleanup-Image /Restorehealth Explained

What is DISM.EXE /Online /Cleanup-image /RestoreHealth? "DISM.exe /Online /Cleanup-Image /Restorehealth" is a DISM command that repairs issue with the...
DHCP Relay agent

DHCP Relay Agent: Configuration in Windows Server 2016

What is a DHCP Relay Agent? A DHCP Relay Agent allows DHCP clients in a different network subnet to...

TRENDING POSTS

Remote Desktop Connection

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

Find My Samsung: Register and Use Samsung Find my Mobile

Introduction Ever wondered how you could find your Samsung phone if you lost it? Find my Samsung or Samsung...
What is the Difference Between PowerShell and CMD?

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...
Spotify No Longer Supports this Version of Microsoft Edge

Spotify No Longer Supports this Version of Microsoft Edge [Fixed]

Introduction When you open Spotify web player on Microsoft Edge, you may receive the error message "Spotify No Longer...
Windows 10 Won't Boot

Windows 10 Won’t Boot With Black Screen? 3 Ways to Fix It

Why Won't Windows 10 Boot Up? If your Windows 10 stops with a black screen, the first question in...

BEST OF ITECHGUIDES

file too large for destination file system

The File is Too Large for the Destination File System [Fixed]

What is the cause of "The File is Too Large for the Destination File System" Error? The "The File...
Excel Count

Excel Count: How to Count in Excel With Examples

Introduction Excel COUNT Function is used for counting items in a worksheet. Excel COUNT also has the conditional function,...
windows has stopped this device code 43

How to Fix Windows Has Stopped this Device Code 43 Error

Introduction Sometimes you may receive "Windows has stopped this device code 43" error. This error is likely to come...
Powershell Function

PowerShell Function: Syntax, Parameters, Examples

Introduction There are two types of PowerShell Functions, basic and advanced. A basic PowerShell Function is a list of...
dhcp relay agent windows server 2016 not working

DHCP Relay Agent Windows Server 2016 Not Working [Fixed]

Introduction Most reports about DHCP relay agent not working in Windows Server 2016 has to do with clients in...

RECENT POSTS

disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because you...
bootrec /fixboot access is denied

How to Fix BootRec /FixBoot Access is Denied Error in Windows 10

Introduction BootRec /FixBoot Access is Denied Error in Windows 10? This error is likely caused by corrupt EFI directory.
Reset Windows 10

How to Reset Windows 10 (2 Methods)

Introduction If your Windows 10 is broken, one available way to fix it is to use Windows 10 reset....
system restore windows 10

How to Enable and Use System Restore in Windows 10

Introduction System restore in windows 10 is a very important tool that is probably ignored by most users. But...
windows has stopped this device code 43

How to Fix Windows Has Stopped this Device Code 43 Error

Introduction Sometimes you may receive "Windows has stopped this device code 43" error. This error is likely to come...
Windows could not automatically detect this network's proxy settings

How to Fix “Windows Could not Automatically Detect this Network’s Proxy Settings”

Introduction You suddenly lose the ability to connect to the internet. Then you run network troubleshooter and it returns...
printer offline

5 Ways to Restore Your Printer Online If Status is Offline

Introduction Is your printer offline? Setting it online is very simple but sometimes it is more than just setting...

How to Fix “BootMgr is Missing” Error in Windows 10

Introduction If you receive Fix "BootMgr is missing" Error in Windows 10, the default response is panic! But you...
windows 10 search featured

How to Fix Windows 10 Search If it Stops Working

Introduction Are you having troubles with Windows 10 search? Apparently, it is a fairly common problem with a number...
oooops, something went wrong. reload

How to Fix “Oooops something went wrong. reload” Spotify Error

Introduction If you receive "Oooops something went wrong. reload" Spotify error, do not panic. The fix is simpler than...

MUST READ

Excel Not Equal

Excel “Not Equal” Comparison Operator: Syntax, Examples

Introduction Excel "Not Equal" is a Comparison operator that compares two values. "Not Equal" will return TRUE if the...
disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because you...
dropbox login

Dropbox Login: Your Ultimate Guide to Dropbox

Introduction Dropbox login allows you to sign in to and use Dropbox. But what is Dropbox? Let's kick off...
Check Disk (CHKDSK) Windows 10

Check Disk (CHKDSK) in Windows 10: Syntax, Parameters, Examples

Introduction Check Disk (CHKDSK) is a Windows 10 command tool that checks a disk for errors, fixes errors, recovers...
How to Make a Line Graph in Excel and Google Sheets

How to Make a Line Graph in Excel

Introduction A line graph is a visual representation of a set of data. This guide shows you how to...

By using this website you agree to accept our Privacy Policy and Terms & Conditions