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.
Search JabSto ::

Custom Search