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

Advertisements

About bbluford
I am an executive finance professional with a love for process and application development (MS Access, Excel, Quickbooks), mostly as it relates to Accounting and Business Functions. I also love to write and share ideas with other people in this world. I'm an admitted Gym Rat who works out excessively. The best summation of me is that I love to teach and to learn.

One Response to Trimmed Mean

  1. Pingback: Trend and Growth Functions « BobbyBluford.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: