How to Calculate Variance in Excel?
Wait!!
What does variation mean in real life?
Some of you may be unaware of it. Let me first explain where you can notice the use of variance.
Example 1: You’ve probably heard that the stock market has an average return. It doesn’t imply you get that return every year. It can be lower or greater the next year. This is the stock market’s variance.
Assume you drive to work every day. But you’ve noticed that your everyday arrival time varies. The variance could be due to traffic, stop-light timing, etc. Also, the gas’s fluctuation is evident. All of these variables can be calculated using variance.
Why use Excel for variance? The short answer is:
- Excel provides various statistical formulas.
- Excel can also assist you manage massive data sets from many sources.
- Excel also simplifies your work.
Now let’s look at how to calculate variance in Excel. But first, what is variance? (technically).
Is there a discrep
It computes the spread numbers in a data set. Or it is the measurement of the data set’s variability that represents the spread of a differential value. It is the average of the squared estimated difference from the mean.
Let us use an example to better understand how to compute variance in excel:
Assume a zoo has five monkeys aged 10, 6, 14, 8, and 2 years. Find its variance.
1.
Sum of these five numbers:
10+6+14+8+2/5 = 8
1.
Make a note of the difference. Plot the difference as:
1.
Calculate each difference.
2.
Calculate the average square difference:
6 + 2 + 0 + (-2)2 + (-6)2/5 = 16.
So the data variance is 16.
Considerations when calculating Variance in Excel
Here are some things to know before calculating variance. Consider this:
Technically, VAR.S can replace the Excel function for data. Also, VAR.P for the population data set must replace VARP.
Use VARPA or VARA to count logical or text values (for samples).
A single parameter (value 1) is required for a cell range reference. That’s a waste of time. So use more arguments.
Users can also reference cell ranges or values individually.
VARP, VAR, and VARP only support numeric values.
Calculating variance in Excel using Functions
In Excel, the variance can be calculated using VAR.S, VAR.P, VARPA, VAR, VARP, and VARA. Any of the variance formulas can be used to determine:
- Using population or sample variation.
- The excel version used.
- Ignore or assess logical and text values.
Excel’s variance functions
We have described the many functions available in Excel to assist you choose the right formula.
Excel version Name Logical and Text
VAR.S 2010-2019 Sample Ignored
VAR.P 2010-219 Population Ignored
VARPA 2000-2019 Population Evaluated
VAR 2000-2019 Sample Ignored
VARP 2000-2019 Population Ignored
N/A for VARA 2000-2019
How to compute variance in Excel (sample)
The sample is the collection of data collected from the entire population, and the sample variance is the variance used to calculate the sample value.
For example, to compute an individual’s height, a sample of 1000 people is used to estimate the population’s height.
Example variance formula:
This is the simple average of the supplied values, and n is the sample number.
The three sample variances are then utilized to determine VAR, VAR.S, and VARA.
Excel VAR sample variance function
It is one of the oldest excel tools to predict sample variance. The VAR function is available in Excel 2000 through 2019.
VAR (number1,2,…)
NOTE: In 2010, VAR.S replaced this function, enhancing accuracy. However, VAR is now accessible for backup compatibility, and VAR.S is used to calculate variance in Excel.
Excel VAR.S sample variance function
It is one of the newer alternatives to Excel 2010 and later.
(number1, [number2],…)
Excel VARA sample variance function
It returns the sample variance based on the text, numbers, and logical values.
(value1, [value2],…)
Excel sample variance example
In order to calculate the sample variance of an excel sheet, one can utilize the functions. As an example, calculate the sample variance of the 6 items (B2: B7). then the formula is:
VAR (B2: B7)
=VAR (B2: B7)
VRA (B2: B7)
Video of simple variance
To validate the data’s accuracy, let’s do it manually:
1.
Calculate the average in cell B8 using the AVERAGE function:
= MEAN (B2: B7)
1.
Subtract the estimated average value from each sample number and enter it in column C2.
=B2-$B$8.
1.
Fill up the blanks in column D2 with the following data.
=C2^2
1.
Add the square of the difference and divide the result by the sample minus 1:
(D2: D7) (6-1)
As shown in the table below:
Excel Variance Calculator (population)
The population is the observable value in a subject of research. It describes the entire population’s data points. The population variance formula is:
Where x bar is the population mean and n is the population size.
Excel has three functions for measuring population variance: VARP, VAR.P, and VARPA.
VARP function in Excel
This function returns the population variance based on all population numbers. It is available in Excel 2000 through 2019.
VARP(number1,2,…)
NOTE: In Excel 2010, VARP was replaced by VAR.P, and it is recommended that you utilize VAR.P in Excel until the VARP function is restored in future Excel versions.
VAR.P excel population variance function
It is utilized in the enhanced VARP function in Excel 2010 and beyond.
VAR.P (number1,…)
Excel VARPA population variance function
It measures the population variance for the set, logical value, and text. Excel 2000-2019 use it.
VARA (Value1,2,…)
Excel population variance example
In this case, the maximum exam score is 5 pupils, which is the maximum for the entire set. If you collect data from students, you may determine the population variance.
For ten students (B2:B11), the variance is determined as follows:
VARP (B2: B11)
VR.P (B2: B11)
VARP (B2: B11)
Video of simple variance
The manual var can be calculated in Excel and judged as follows:
How to Calculate Variance in Excel
To compute the variance in Excel, use the following simple norms:
- Arrays, values, or cell references
- Excel 2007 and later allow up to 255 arguments per population or sample, while Excel 2003 and earlier allow just 30 arguments.
- Ignore text, empty cells, and logical values to get references. Calculate sample variance with VAR.S or VAR, and population variance with VAR.P or VARP.
- VARPA or VARA functions can get text and logical values in references.
- In the Excel sheet, enter two numbers for the sample variance formula and one for the population variance formula.
- The text in the arguments cannot be translated and cause #VALUE! Numbering errors.
Conclusion
This blog has offered information on how to compute sample and population variance in Excel. These functions work with Excel versions 2000 to 2019. These functions are simple to use and perform.
So, if you need to determine the variance of Excel data, utilize the above tools. If you have any questions about statistics assignments, you can contact our professionals who are known for their original content. They can deliver data on time, and our services are available 24/7. So use our statistical services and get A+ ratings. Experts aid with excel assignments.
Also See
- Z Score in Excel
- Excel Find Range
- Using Excel Functions to Calculate Variance
Questions & Answers
What is a variation?
In statistics and probability, variance measures how widely a set is spread apart. It also shows how far the random figure is from the intended value. Variance is the average of the squares of the observed and predicted values.
What are the types of variation?
Variations come in many forms. Among them:
Price Variations
VARIANCE OF OVERHEAD
Profit Continuity.
Labor Variations
Material alterations
Overhead Variance
Variance in sales
