Microsoft Office Tutorials and References
In Depth Information
Stuck in the Middle with AVERAGE, MEDIAN, and MODE
But can you just drop a customer like that (not to mention the biggest
customer)? Yikes! Instead, you can consider a couple of creative averaging
solutions. Either use the median or use a weighted average (a calculation
of the mean but in which the relevance of each value is taken into account).
Figure 9-3 shows the result of each approach.
Scenario 1 shows the mean and the median for the set of customer amounts.
Here, using the median is a better representation of the central tendency of
When reporting results based on an atypical calculation, it’s good practice to
add a footnote that explains how the answer was determined. If you were to
report that the “average” expenditure was $925, a note should explain this is
the median, not the mean.
Scenario 2 in Figure 9-3 is a little more complex. This involves making a
weighted average, which is used to let individual values be more or less
influential in the calculation of a mean. This is just what you need! Customer E
needs to be less influential.
Weighted averages are the result of applying a weighting factor to each value
that is used in calculating the mean. In this example, all the customers are
given a weight factor of 18 except Customer E, who has a weight factor of
10. All customers except Customer E have been given increased weight, and
Customer E has been given decreased weight because his sales value is so
different from all of the others. When weights are applied in an average, the
sum of the weights must equal 100. Without applying any weighting factor,
each customer effectively has a weight of 16.667 — the number of
customers divided into 100. Applying a weight of 10 to Customer E, and 18 to all the
other customers, keeps the sum of the weights at 100: 18 × 5 + 10. The values