How to Find Variance in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office 125 125 people found this article helpful

How to Find Variance in Excel

Calculating the spread of your data using variance and standard deviation

By Jody Emlyn Muelaner Jody Emlyn Muelaner Writer University of Bath Dr. Jody Muelander is a former freelance contributor to Lifewire who's writing has appeared in peer-reviewed journals and aerospace industry reports. lifewire's editorial guidelines Updated on April 21, 2022 Reviewed by Jessica Kormos Reviewed by Jessica Kormos Saint Mary-of-the-Woods College Jessica Kormos is a writer and editor with 15 years' experience writing articles, copy, and UX content for Tecca.com, Rosenfeld Media, and many others. lifewire's editorial guidelines Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook

What to Know

Use the VAR.P function. The syntax is: VAR.P(number1,[number2],...)To calculate standard deviation based on the entire population given as arguments, use the STDEV.P function.
This article explains data summarization and how to use deviation and variance formulas in Excel for Microsoft 365, Excel 2019, 2016, 2013, 2010, 2007, and Excel Online.

Summarizing Data Central Tendency and Spread

The central tendency tells you where the middle of the data is, or the average value. Some standard measures of the central tendency include the mean, the median, and the mode. The spread of data means how much individual results differ from the average. The most straightforward measure of spread is the range, but it's not very useful because it tends to keep increasing as you sample more data. Variance and standard deviation are much better measures of spread. The variance is simply the standard deviation squared. A sample of data is often summarized using two statistics: its average value and a measure of how spread out it is. Variance and standard deviation are both measures of how spread out it is. Several functions let you calculate variance in Excel. Below, we'll explain how to decide which one to use and how to find variance in Excel.

Standard Deviation and Variance Formula

Both the standard deviation and the variance mesure how far, on average, each data point is from the mean. If you were calculating them by hand, you would start by finding the mean for all your data. You would then find the difference between each observation and the mean, square all those differences, add them all together, then divide by the number of observation. Doing so would give the variance, a kind of average for all the squared differences. Taking the variance's square root corrects the fact that all the differences were squared, resulting in the standard deviation. You will use it to measure the spread of data. If this is confusing, don't worry. Excel does the actual calculations.

Sample or Population

Often your data will be a sample taken from some larger population. You want to use that sample to estimate the variance or standard deviation for the population as a whole. In this case, instead of dividing by the number of observation (n), you divide by n-1. These two different types of calculation have different functions in Excel: Functions with P: Gives the standard deviation for the actual values you have entered. They assume your data is the whole population (dividing by n).Functions with an S: Gives the standard deviation for a whole population, assuming your data is a sample taken from it (dividing by n-1). It can be confusing, as this formula provides the estimated variance for the population; the S indicates the dataset is a sample, but the result is for the population.

Using the Standard Deviation Formula in Excel

To calculate the standard deviation in Excel, follow these steps. Enter your data into Excel. Before you can use the statistics functions in Excel, you need to have all your data in an Excel range: a column, a row, or a group matrix of columns and rows. You need to be able to select all the data without selecting any other values. For the rest of this example, the data is in the range A1:A20. If your data represents the entire population, enter the formula "=STDEV.P(A1:A20)." Alternatively, if your data is a sample from some larger population, enter the formula "=STDEV(A1:A20)." If you're using Excel 2007 or earlier, or you want your file to be compatible with these versions, the formulas are "=STDEVP(A1:A20)," if your data is the entire population; "=STDEV(A1:A20)," if your data is a sample from a larger population. The standard deviation will be displayed in the cell.

How to Calculate Variance in Excel

Calculating variance is very similar to calculating standard deviation. Ensure your data is in a single range of cells in Excel. If your data represents the entire population, enter the formula "=VAR.P(A1:A20)." Alternatively, if your data is a sample from some larger population, enter the formula "=VAR.S(A1:A20)." If you're using Excel 2007 or earlier, or you want your file to be compatible with these versions, the formulas are: "=VARP(A1:A20)," if your data is the entire population, or "=VAR(A1:A20)," if your data is a sample from a larger population. The variance for your data will be displayed in the cell. FAQ How do I find the coefficient of variation in Excel? There's no built-in formula, but you can calculate the coefficient of variation in a data set by dividing the standard deviation by the mean. How do I use the STDEV function in Excel? The STDEV and STDEV.S functions provide an estimate of a set of data's standard deviation. The syntax for STDEV is =STDEV(number1, [number2],...). The syntax for STDEV.S is =STDEV.S(number1,[number2],...).
Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Use the STDEV Function in Excel How to Use the Excel MID Function How to Use the ISBLANK Function in Excel Ignore Error Values When Finding the Average in Excel How to Combine the ROUND and SUM Functions in Excel How to Use the COUNTIF Function in Excel How to Use the Excel DATE Function How to Use the Excel INDEX Function How to Calculate Variance in Excel How to Use the Excel TRUNC Function How to Calculate Weighted Averages in Excel With SUMPRODUCT How to Use the IF-THEN Function in Excel How to Use Excel's MROUND Function How to Use Excel's Small and Large Function How to Calculate Percentage in Excel Excel QUOTIENT Function: Divide Numbers Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies