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.

Figure 9-3:

Calculating

a creative

mean.

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

the group.

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