Trimmed Mean

Companies are always looking for trends.  One common statistic is average.  What is the average monthly expenditure for travel and entertainment?  On average, how many units of each SKU are sold?  How many calls per day, on average, does our Call Center receive?  All these are common questions and present a picture for management to look at in an attempt to better understand the business.

But just taking the average can be misleading and problematic.  What if there was a trade show in July and the entire Sales and Management Teams went to Phoenix, Arizona for a week?  That would obviously skew the travel and entertainment numbers.  During the Holidays, mainly because it’s the end of the year, your company sells twice as many calendars as it does the rest of the year.  So forecasting for February based on the annual average would be a mistake.  And let’s take the example of the Call Center.

In looking at a two-week period, you notice that, because of an outage in the data center on 12/7, there were an inordinate number of calls taken the following day.  Or, in another scenario, the phone lines went down on 3/11, so no calls were received.  In both of these cases, the average doesn’t completely tell the story.  A better approach in this and many cases is the TrimMean formula, which removes from the equation any outliers that might muddy the picture.  In fact, I’d argue that trimming the data, at least a little bit, almost never hurts.

The formula is pretty simple and takes the following format:
=trimmean (list or array to average, percentage of list to remove)

The second part is tricky because you fairly, if ever, know the exact percentage of items you wish to remove.  You also must take an equal number from both sides (high and low) of the list, which makes things even more difficult.  The way around that is to first determine how many items to remove (at least 2; always a multiple of 2); then divide that by the total number of items.  So, if n is the number of items you want to take from each side (top and bottom) and N is the total size of the list, then (n*2)/N gives you the percentage to remove.

So, replacing the second part of the function:
=trimmean (list or array to average, (n*2)/N)

You then have an average that is of better use and is more realistic.

For an example, please see attached: Trimmean Function Example
TrimmedMean