Microsoft Office Tutorials and References

In Depth Information

**Deviating from the Middle**

of 18 and 10 have been subjectively chosen. When you use weighting factors

to calculate a weighted average you must make that fact known when you

present the results.

The mean in Scenario 2 is $3,711. This figure is still way above the median or

even the mean of just the five customers without Customer E; refer to Figure

9-2. Even so, it’s less than the unweighted mean shown in Scenario 1 and is

probably a more accurate reflection of the data.

By the way, the mean in Scenario 2 is not calculated with the AVERAGE

function, which cannot handle weighted means. Instead, the SUMPRODUCT

function is used. The actual formula in cell F18 looks like this:

=SUMPRODUCT(F9:F14,G9:G14)/SUM(G9:G14)

The amount that each customer spent last year is multiplied by that

customer’s weight, and a sum of those products is calculated with SUMPRODUCT.

Finally, the sum of the products is divided by the sum of the weights.

Deviating from the Middle

Life is full of variety! Calculating the mean for a group will not reflect that

variety. Suppose you are doing a survey of salaries for different occupations

and that occupation A has a mean salary of $75,000 a year and occupation B

has the same mean of $75,000 a year. Does this mean that the two groups are

the same? Not necessarily. Suppose that in group A the salaries range from

$65,000 to $85,000, but in group B they range from $35,000 to $115,000. This

difference — how much the values differ from the mean — is called
variance.

Excel provides you with functions to calculate and evaluate variance, and

this is an important part of many statistical presentations.

Measuring variance

Variance is a measure of how spread out a set of data is in relation to the mean.

Variance is calculated by summing the squared deviations from the mean.

Mathematics that make you work

Specifically, variance is calculated as follows:

1. Calculate the mean of the set of values.

2. Calculate the difference from the mean for each value.

3.Squareeachdifference.