VAR

Returns the sample variance.

Syntax:

VAR(number1, number2, ... number30)

number1 to number30 are up to 30 numbers or ranges containing numbers.

VAR returns the variance where number1 to number30 are a sample of the entire population. With N values in the sample, the calculation formula is:

Example:

VAR(2, 6, 4)

returns 4.

Application:

Imagine you are the manager of a small startup and you want to analyze the consistency of your sales team's performance over the last quarter. You have three salespeople: Alex, Beth, and Chris. You've recorded their monthly sales (in thousands of dollars) for April, May, and June.

You decide to use the VAR function to calculate the sample variance of their sales data. This will help you understand how much the sales figures for each person vary from their average sales. A lower variance would indicate more consistent performance, while a higher variance would suggest more fluctuations.

Here is the sales data in a table:

	Salesperson	April Sales ($k)	May Sales ($k)	June Sales ($k)
	A	B	C	D
1	Alex	25	28	27
2	Beth	20	35	25
3	Chris	30	32	29

Let's calculate the sample variance for each salesperson using the VAR function. The formula for sample variance is:

$s^{2} = \frac{\sum _{i = 1}^{n} ( x _{i} - x ˉ ) ^{2}}{n - 1}$

Alex's Sales Variance:

1. Calculate the average ( $\overset{x}{ˉ}$ ) of Alex's sales:

$\overset{x}{ˉ} = \frac{25 + 28 + 27}{3} = \frac{80}{3}$

2. Calculate the squared difference from the mean for each month:

April: $(25 - \frac{80}{3})^{2} = (\frac{75}{3} - \frac{80}{3})^{2} = (- \frac{5}{3})^{2} = \frac{25}{9}$
May: $(28 - \frac{80}{3})^{2} = (\frac{84}{3} - \frac{80}{3})^{2} = (- \frac{4}{3})^{2} = \frac{16}{9}$
June: $(27 - \frac{80}{3})^{2} = (\frac{81}{3} - \frac{80}{3})^{2} = (- \frac{1}{3})^{2} = \frac{1}{9}$

3. Sum the squared differences: $\frac{25}{9} + \frac{16}{9} + \frac{1}{9} = \frac{42}{9} = \frac{14}{3}$

4. Apply the VAR function (sample variance formula):

$V A R (25, 28, 27) = \frac{\frac{14}{3}}{3 - 1} = \frac{\frac{14}{3}}{2} = \frac{14}{6} = \frac{7}{3} \approx 2.333$

Beth's Sales Variance:

1. Calculate the average ( $\overset{x}{ˉ}$ ) of Beth's sales:

$\overset{x}{ˉ} = \frac{20 + 35 + 25}{3} = \frac{80}{3}$

2. Calculate the squared difference from the mean for each month:

April: $(20 - \frac{80}{3})^{2} = (\frac{60}{3} - \frac{80}{3})^{2} = (- \frac{20}{3})^{2} = \frac{400}{9}$
May: $(35 - \frac{80}{3})^{2} = (\frac{105}{3} - \frac{80}{3})^{2} = (\frac{25}{3})^{2} = \frac{625}{9}$
June: $(25 - \frac{80}{3})^{2} = (\frac{75}{3} - \frac{80}{3})^{2} = (- \frac{5}{3})^{2} = \frac{25}{9}$

3. Sum the squared differences: $\frac{400}{9} + \frac{625}{9} + \frac{25}{9} = \frac{1050}{9} = \frac{350}{3}$

4. Apply the VAR function (sample variance formula):

$V A R (20, 35, 25) = \frac{\frac{350}{3}}{3 - 1} = \frac{\frac{350}{3}}{2} = \frac{350}{6} = \frac{175}{3} \approx 58.333$

Chris's Sales Variance:

1. Calculate the average ( $\overset{x}{ˉ}$ ) of Chris's sales:

$\overset{x}{ˉ} = \frac{30 + 32 + 29}{3} = \frac{91}{3}$

2. Calculate the squared difference from the mean for each month:

April: $(30 - \frac{91}{3})^{2} = (\frac{90}{3} - \frac{91}{3})^{2} = (- \frac{1}{3})^{2} = \frac{1}{9}$
May: $(32 - \frac{91}{3})^{2} = (\frac{96}{3} - \frac{91}{3})^{2} = (- \frac{5}{3})^{2} = \frac{25}{9}$
June: $(29 - \frac{91}{3})^{2} = (\frac{87}{3} - \frac{91}{3})^{2} = (- \frac{4}{3})^{2} = \frac{16}{9}$

3. Sum the squared differences: $\frac{1}{9} + \frac{25}{9} + \frac{16}{9} = \frac{42}{9} = \frac{14}{3}$

4. Apply the VAR function (sample variance formula):

$V A R (30, 32, 29) = \frac{\frac{14}{3}}{3 - 1} = \frac{\frac{14}{3}}{2} = \frac{14}{6} = \frac{7}{3} \approx 2.333$

Summary and Conclusion:

	Salesperson	Sample Variance
	A	B
1	Alex	2.333
2	Beth	58.333
3	Chris	2.333

Based on the VAR function results:

Alex and Chris have a very low sample variance (2.333). This indicates that their monthly sales figures are very consistent and don't fluctuate much from their respective averages.
Beth has a significantly higher sample variance (58.333). This shows a high degree of variability in her monthly sales, with a wide swing from a low of $20k to a high of $35k.

As the manager, this analysis tells you that while all three salespeople have solid average performance (Alex and Beth even have the same average), Beth's performance is less predictable. You might want to meet with Beth to understand the reasons for these fluctuations and see if you can help her achieve more consistent results.

This page is protected by Google reCAPTCHA. Privacy - Terms.

Built using Zapof