When we are dealing with mathematics and especially with the field of statistics, knowing the concept of variance and learning how to calculate it can be quite important to solve some questions. Microsoft Excel can even help you in this endeavour, as it has a formula that calculates this measure and can be applied to your spreadsheets. See below how to calculate variance in Excel in a simple and straightforward way.
what is variance
Understood as the measure of the dispersion of a given set of data in relation to its mean, the variance may seem like a seven-headed animal, but the truth is that its concept is simpler to understand when we are dealing with examples.
For this, let’s imagine a school, whose ninth-grade students took five tests throughout the month of November. When we add the grades of one of these students (let’s call him James) and divide them by five (the total of tests that were given), we get the average of the boy’s grades during that month, correct?
Having done this and having the value of this average, we can also discover the variance of James’ grades in November. This means that by applying the variance formula to this data, we can find out how much the boy’s grades fluctuated more or less throughout the entire month, in relation to his own average.
If James’ variance is small, it means that his scores were very close to each other. Now, if his variance is high, that means he was fickle throughout November, sometimes getting higher grades and sometimes getting lower grades.
The variance in Excel
When working with a dataset in Excel, we are able to discover a variance using a simple formula in the editor, without having to calculate its average beforehand. Below, we explain step-by-step how to apply this formula in your spreadsheet, adjusting it to the data you have:
- Check your data: Make sure all the data you need is in your spreadsheet. In the suggested example, we are dealing with all the ninth graders of a school and the grades taken by each of them in the five tests applied in November. It is important that, as in the example, all your data is arranged in order, whether in columns or rows;
- Apply the variance formula: Click on the cell in which you want to enter the variance value of the grade for a particular student. In the example, John’s variance score. Paste the formula =VAR.P(number1,[number2],…), where VAR.P is the population variance value we want to find; number1 is the cell where our sample data begins, in this case, John’s grade on the first test; and number 2 is the cell where these data end, in this case, John’s grade in the fifth test.
- Make the necessary changes to the formula: Change the formula according to your data distribution in the worksheet. In our case, John’s variance formula will be =VAR.P(E2:E6), since the space between E2 and E6 encompasses all the evaluations that the student had in November;
- Press Enter: Press the Enter key and watch the result of John’s variance appear on the screen. In the case 2.2664.
- Calculate all your variances: If there are more variances that you want to find, follow steps 2, 3, and 4 again for each cell in question, fitting its components to the evaluated data.
Population Variance x Sample Variance
When dealing with variance in Excel, it is important to keep in mind that there are two types of variance that can be calculated: population and sample.
In the example above, we used population variance (VAR.P) in our calculations because we had all the data we needed, in this case, all the grades taken by ninth graders in November.
Imagine, however, if instead of the variance of students’ grades in a single month, we wanted to assess the variance they had throughout the entire school year. In this case, as we do not have all these grades, but only a sample of them, what we calculate is the sample variance (VAR.A) of the evaluations of these students.
In practice, this means that instead of the formula in the example above, to calculate the sample variance we use the formula =VAR.A(number1,[number2],…).
