Advertisements

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.
Advertisements


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

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

LATEST DEALS

Exclusive Student Offer_Save 10% on selected Surface devices

FEATURED POSTS

How to Change Administrator Name in Windows 10 (4 Methods)

How to Change Administrator Name in Windows 10

Introduction This guide demos how to change administrator name in Windows 10. There could be...
How to Setup WhatsApp Auto Reply in an Android Phone

How to Setup WhatsApp Auto Reply in an Android Phone

Introduction This guide demos how to setup Whatsapp auto reply in an Android phone. Steps...
How to Set Out of Office Auto Reply in Outlook, Outlook.com and Gmail

How to Set Out of Office Auto Reply in Outlook, Outlook.com and Gmail

Introduction This guide demos how to set out of office auto reply in Outlook, Outlook.com and Gmail.
How to Take Ownership of Folder in Windows 10

How to Take Ownership of Folder in Windows 10 (2 Methods)

Introduction This guide demos 2 methods to take ownership of folder in Windows 10. Options to...
How to Install Windows Server 2019 from USB

How to Install Windows Server 2019 from USB

Introduction This guide demos how to install Windows Server 2019 from USB. Steps to Install...

ADVERTISEMENTS

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

How to Install Windows 10 from Network Boot (Via WDS Server)

Introduction This guide demos how to install Windows 10 from network boot. The steps discussed in...
How to Delete Hotmail Account (Outlook.com Account)

How to Delete Hotmail Account (Outlook.com Account)

Introduction This guide demos how to delete Hotmail account. Hotmail.com has been replaced by Outlook.com.

How to Check FSMO Roles Holders in Active Directory

Introduction This guide illustrates how to check FSMO roles in Server 2016. The are 5...
avast vpn

Avast VPN: How to Install, Setup and Stay Safe Online

What is Avast Secureline VPN? Avast VPN is a VPN (Virtual Private Network) software that allows you to...
gmail archive

How to Archive and Unarchive Emails in Gmail

Introduction When you archive Gmail emails it helps to free up your inbox. In Gmail archived emails are moved...

RECENT POSTS

C:\G-Drive\Work Tools\Products Portal\1. New Business\2. Content Sites\1. iTechGuides.com\Posts\1. HOW TO\Microsoft\MS Office\Microsoft Word\how to add more rows to a table in word

How to Add More Rows to a Table in Word and Google Docs

Introduction This guide demos how to add more rows to a table in Word. It also has a...
How to Create a Bar Chart in Excel and Google Sheets

How to Create a Bar Chart in Excel and Google Sheets

Introduction This guide demos how to create a bar chart in Excel and Google Sheets. A bar chart is...
How to Change Administrator Name in Windows 10 (4 Methods)

How to Change Administrator Name in Windows 10

Introduction This guide demos how to change administrator name in Windows 10. There could be...
How to Create a Pie Chart in Excel and Google Sheets

How to Create a Pie Chart in Excel and Google Sheets

Introduction This guide demos how to create a pie chart in Excel and Google Sheets. A pie chart is...

iTechGuides’ Top 10 Online Games

The gaming world is getting bigger day by day. Many people are getting engaged to play online games worldwide.

How to Open Local Security Policy in Windows 10 (5 Methods)

Introduction This guide demos how to open local security policy in Windows 10. Options to...

How to Change Network from Public to Private in Windows 10

Introduction This guide demos 2 methods to change network from public to private in Windows 10.
How to Disable Touchpad in Windows 10 for Dell or HP Laptop

How to Disable Touchpad in Windows 10 for Dell or HP Laptop

Introduction This guide demos how to Disable Touchpad in Windows 10 for a Dell or HP laptop.

How to Change Account Picture in Windows 10 (2 Methods)

Introduction This guide demos 2 methods to change account picture in Windows 10. Windows 10...
How to Create Xbox Live Account

How to Create Xbox Live Account (2 Methods)

Introduction This guide demos 2 methods to create Xbox Live account. Options to Create Xbox...

ADVERTISEMENTS

MUST READ

how to move columns in excel

How to Move Columns to Rows and Rows to Columns in Excel

Introduction If you receive some Excel data in columns, you can easily move the columns to rows in Excel...
Wifi doesn't have a valid IP configuration

How to Fix “Wifi Doesn’t Have a Valid IP Configuration” Error

Introduction "Wifi doesn't have a valid IP configuration" error is common among Windows users. When you receive this error...
0xc000021a: Status System Process Terminated Error [Fixed]

0xc000021a: Status System Process Terminated Error [Fixed]

Introduction You may receive "0xc000021a (STATUS_SYSTEM_PROCESS_TERMINATED)" error after installing a new device on your Windows computer. You may also...
ps4 action games

Our Top 10 PS4 Action Games

Looking for PS4 action games? We have you covered! "God of War" is our number 1 for its excellent...
Dual WhatsApp account in one phone

How to Use Dual WhatsApp Accounts in One Android Phone

Introduction Most Android phones now support dual SIM. If your Android phone has 2 SIM cards you can configure...

LATEST DEALS

Exclusive Student Offer_Save 10% on selected Surface devices

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