VARPA

Returns the population variance (allowing text and logical values).

Syntax:

VARPA(number1, number2, ... number30)

number1 to number30 are up to 30 numbers or ranges containing numbers. Logical values and text may also be included.

VARPA returns the variance where number1 to number30 are the entire population. If you only have a sample of the population use VARA instead.

Logical values are regarded as 1 (TRUE) and 0 (FALSE).

Text values are always regarded as zero.

For a population of N values, the calculation formula is:

$\frac{1}{N} \sum_{i = 1}^{N} (x_{i} - \overline{x})^{2}$

Example:

VARPA(3, 3, 7, 7)

returns 4.

VARPA(B1:B4)

where cells B1, B2, B3, B4 contain red, FALSE, 4, 4 returns 4, the population variance of 0, 0, 4 and 4.

Application:

Scenario:

A small business wants to understand the dispersion of salaries across its entire workforce. Since they have data for every single employee, this is considered a population. To measure how much the salaries vary from the average, they decide to calculate the population variance using the VARPA function in a spreadsheet.

The Data Table:

The following table lists the annual salary for each of the company's employees. All values are numeric.

	Employee ID	Annual Salary
	A	B
1	001	$50,000.00
2	002	$55,000.00
3	003	$60,000.00
4	004	$62,000.00
5	005	$70,000.00

Analysis with VARPA

The VARPA function is used because the business is analyzing the entire population of salaries. The function calculates the variance, which is the average of the squared differences from the mean. A higher variance would indicate that the salaries are more spread out, while a lower variance would show that they are more closely clustered around the average.

The VARPA function is entered to calculate the variance for the salaries listed in the table.

Formula:

Assuming the salaries are in a column from cell B2 to B6, the formula would be:

VARPA(B2:B6)

Calculation:

The VARPA function performs the following steps on the dataset {50000, 55000, 60000, 62000, 70000}:

Calculate the mean (μ): (50000+55000+60000+62000+70000)/5=59,400
Find the squared difference of each value from the mean:
- (50000−59400)2=88,360,000
- (55000−59400)2=19,360,000
- (60000−59400)2=360,000
- (62000−59400)2=6,760,000
- (70000−59400)2=112,360,000
Sum the squared differences and divide by the total number of data points (5): (88,360,000+19,360,000+360,000+6,760,000+112,360,000)/5=227,200,000/5=45,440,000

Result:

The VARPA function returns a population variance of 45,440,000. This number provides a measure of how widely the salaries are distributed across the entire company.

Result:

45,440,000