VAR.S

Calculates the variance of a sample of data.

Syntax:

VAR.S(numberOne, [numberTwo],…)

numberOne, [numberTwo],… are 1 to 255 arguments representing the numbers that you want to use. These arguments can be numbers, cell references or ranges.

Calculation for VAR.S:

$\frac{\sum ( x - x ˉ ) ^{2}}{( n - 1 )}$

Example:

If numberOne, [numberTwo],… contains 1,1,1,1,2,2,2,2,2,2,3,3,4,4,4,4,5,5,5,5,5:

VAR.S(1,1,1,1,2,2,2,2,2,2,3,3,4,4,4,4,5,5,5,5,5)

returns 2.3

If numberOne, [numberTwo],… contains 1,1,1,1,2,2,2,2,2,3,3,3,4,4,4,4,5,5,5,5,5:

VAR.S(1,1,1,1,2,2,2,2,2,3,3,3,4,4,4,4,5,5,5,5,5)

returns 2.247619048

	A	B	C	D
1	1	1	1	2.247619048
2	1	2	2
3	2	2	2
4	3	3	3
5	4	4	4
6	4	5	5
7	5	5	5

Application:

Analyzing the Punctuality of a Bus Route

A bus company wants to analyze the on-time performance of one of its most popular routes. They decide to collect a sample of the arrival times over a two-week period, measured in minutes early (-) or late (+). They understand that the full population of all arrival times is infinite, so they are using a sample to estimate the variance.

The company uses the VAR.S function to calculate the variance of this sample. A higher variance would indicate more inconsistency in the arrival times, meaning the bus is either very early or very late on different days. A lower variance would suggest more consistent, predictable arrival times.

Sample Data: Bus Arrival Times (in minutes from scheduled time)

	Day	Arrival Time (min)
	A	B
1	Monday	3
2	Tuesday	1
3	Wednesday	-2
4	Thursday	5
5	Friday	0
6	Monday	4
7	Tuesday	2
8	Wednesday	-1
9	Thursday	6
10	Friday	1

To calculate the sample variance using the VAR.S function, you would use the following formula:

VAR.S(3, 1, -2, 5, 0, 4, 2, -1, 6, 1)

Or, if the data is in a range of cells (e.g., B1 to B10), the formula would be:

VAR.S(B1:B10)

Result:

The result of this calculation is 6.766666667

This number, 6.766666667, represents the sample variance of the bus's arrival times. The company can now use this value to assess the consistency of the route's performance. For instance, they might compare this variance to that of other routes to see which one is more reliable.

Result for VAR.S(3, 1, -2, 5, 0, 4, 2, -1, 6, 1):

6.766666667