Excel provides two functions, VAR.P and VAR.S, to calculate the variance of a set of data. Variance measures the degree of spread in a data set. The VAR.P function calculates the variance based on the entire population, while VAR.S is used when you have a sample of the population. These functions are essential in statistics for understanding how data points differ from the mean of the data set.
Syntax
| Syntax | Description |
|---|---|
| VAR.P(number1, [number2], ...) | Calculates the variance based on the entire population. |
| VAR.S(number1, [number2], ...) | Calculates the variance based on a sample of the population. |
Example
Consider a scenario where you have a dataset representing the monthly incomes of employees in two different departments of a company. You want to compare the variability in incomes between these two departments using both VAR.P and VAR.S.
| Employee | Department A Income | Department B Income |
|---|---|---|
| Employee 1 | 4000 | 5000 |
| Employee 2 | 4200 | 5200 |
| Employee 3 | 4500 | 4800 |
| Employee 4 | 4700 | 5300 |
| Employee 5 | 4300 | 4900 |
In this example, you can use VAR.P and VAR.S to calculate the variance for each department’s income. This will help determine which department has more variability in income.
Practice Exercise 1
Task: The following table shows the production output (in units) of five machines over three days. Use Excel to calculate the variance of the production output for each machine using both VAR.P and VAR.S.
Practice Exercise 2
Task: The table below shows the daily temperatures (in degrees Celsius) recorded over a week in two different cities. Calculate the variance of the temperatures for each city using both VAR.P and VAR.S to compare the variability.
Solutions
To solve both exercises, you will use the VAR.P and VAR.S functions in Excel. Here’s how you can calculate the variance for each dataset:
Solution for Exercise 1:
For Machine A in Exercise 1:
- VAR.P formula:
=VAR.P(B2:D2) - VAR.S formula:
=VAR.S(B2:D2)
Repeat these formulas for each machine (B2 for Machine A, B3 for Machine B, etc.).
Solution for Exercise 2:
For City X in Exercise 2:
- VAR.P formula:
=VAR.P(B2:B8) - VAR.S formula:
=VAR.S(B2:B8)
For City Y in Exercise 2:
- VAR.P formula:
=VAR.P(C2:C8) - VAR.S formula:
=VAR.S(C2:C8)
Apply these formulas directly in Excel to obtain the variance values for each of the datasets provided in the exercises.