Variance is a powerful data analysis tool used to calculate how much inpidual results vary from the average value. It is generally used for numerical data in Excel, but it can also measure other types of information. Calculating variance in Excel may seem complicated at first, but once you know what each function is used for, you can do it in seconds.
This article will teach you how to find the variance in Excel on Windows PC, Mac, and iPad. We’ll also explain what each function is used for and how they can help you calculate and estimate various types of data.
How to Find Variance in Excel on Windows PC
Calculating variance in Excel means measuring how far different values are from the average value (or mean). In Excel, finding the variance generally refers to calculating a set of numbers in a range of data with respect to their average value.
In simple terms, we use variance in Excel to determine how much inpidual results vary from the average result. The more the numbers grow, the greater the variation. However, if the variance is equal to zero, all numbers in the data set are equal.
In Excel, the variation tool can be used to calculate various metrics. For example, the age group of a population, test results, expenses, and more.
There are different types of variance functions in Excel, depending on the type of variance and the size of your data set. There are six basic variance functions that you can use to calculate sample variance and population variance. These include VAR, VAR.P, VARP, VAR.S, VARA and VARPA functions. There are other functions available in Excel, but we won’t need them for this guide.
Before looking at how to find the variance in Excel, you need to determine which variance function to use. If you have a smaller data set, you can use the VAR, VARA, and VAR.S functions. For a larger sample, the best variance functions are VAR.P, VARP, and VARPA.
The sample variance is used if your spreadsheet includes only a sample of the population, while if the entire population is estimated, you will need to use the population variance. The VAR.P function is used to calculate the variance based on the entire population and ignores logical values and text. The VARA function also measures variance based on the entire population, but does not ignore logical values or text. In contrast, the VAR.S function estimates the variance based on a sample of the population.
The type of variation function you should use depends on the version of Excel you have. For example, the VAR.S function is the newer version of the VAR function. The same goes for the VAR.P function, which is used instead of the VARP function in newer versions of Excel.
Suppose you want to calculate a sample variance in Excel. You want to find the variance of your students’ test scores, where column A is for the students’ names and column B is for their scores.
Follow the steps below to calculate the variance in your Excel spreadsheet.
- Launch Excel on your Windows PC.
- Open the spreadsheet where you want to find the variance. If you want to create a new spreadsheet, select “Blank Workbook.”
- Enter the necessary data into the spreadsheet.
Note– Make sure all data is written in a range (a column or a row) or you won’t be able to find the variation. - Double-click an empty cell anywhere in your spreadsheet.
- Write “
=VAR” (“=VAR.S”if you have the most recent version of Excel) in the cell.
- In parentheses, write the range. For example: “(B2:B11)”.
- Press the “Enter” key.
The variation will appear immediately in the same cell where you wrote the function.
If you want to calculate a population variance, you will use the VARP or VAR.P function, depending on which version of Excel you have. A population variance indicates that the data in your spreadsheet represents the entire population of something. This is how you do it.
- Open Excel and go to your spreadsheet.
- Double-click an empty cell.
- Write “
=VARP” either “=VAR.P” for newer versions of Excel.
- Enter the range in parentheses, as in this example “(A2:A15)”.
- Press “Enter.”
The population variance will appear in that same cell.
How to find variance in Excel on a Mac
If you have a Mac, you can use it to find variations in Excel. If you want to calculate a sample variance for a data set, follow the steps below.
- Run Excel on your Mac.
- Open an old spreadsheet or create a new one.
- Make sure the data is entered in the same range.
- Choose an empty cell anywhere in your spreadsheet and double-click it.
- Enter the sample variance function “
=VAR” either “=VAR.S”.
- Write the range in parentheses. For example: “
(A4:A39)”.
Note: Make sure there is no space between the function and the range. It’s supposed to look like this: “=VAR.S(A4:A39)”. - Press the “Enter” key.
That’s all about it. The sample variance will be calculated immediately in the same cell where you entered the function. If you are working with a larger population, use the VAR.P function. Follow the steps below to find out how it’s done.
- Open your spreadsheet.
- Double-click an empty cell and enter “
=VAR.P”. - Write the range in parentheses, as in this example: “
=VAR.P(B2:B50)”.
- Press “Enter” on your keyboard.
How to find variance in Excel on an iPad
If you don’t have your laptop with you right now, you can use your iPad to find variations in Excel. The process is basically similar, only you will be working on a smaller screen. Here’s what you need to do to calculate variance in Excel on an iPad.
- Open Excel on your iPad.
- Choose a spreadsheet from the “New” or “Recent” sections.
- Tap an empty cell to select it.
- Enter the variation function in the “fx” bar above the spreadsheet. For example: “
=VAR.P(A3:A15)”.
The variation will automatically appear in the cell you had previously selected.
Use Excel’s Variation Functions to Your Advantage
No matter what device you use to find the variation in Excel, it will only take you a few minutes to do so. You just need to figure out which variance function works best with your data set and the rest is easy. You will be able to estimate different types of variances, whether they refer to numerical or logical data.
