If you're an inbound marketer, chances are you have lots of data to analyze and you base your most important decisions on quantitative measures. In this blog series, I discuss some the most useful tools and tricks for analyzing your data in Excel.

Often times we hear statistics based on the average or the mean value of a dataset. The average, however, is often not the most representative number and should be used with caution. This is because the average includes the most extreme values in a dataset, i.e. it gives equal weight to all numbers including the smallest and largest values.
The median, however, does not.  The median is just the datapoint in the middle.  It is not affected by wild outliers and, because of this, it is often more representative of the data than the average.

The example in this dataset shows how the average and the median can tell two different stories. Suppose we have the number of days in the sales cycles of nine customers.  The average number of days is over six weeks (44 days). The median , however tells a very different story, i.e. that a typical sales cycle only lasts about two weeks (16 days). This is because of one crazy outlier (270 days) that is pushing the average up.

Excel provides a MEDIAN function for calculating this value. You can either enter the individual numbers separated by commas, or you can enter a range of cells with numbers (i.e. an array ). The formula in this example at right would return "16".
An alternative to the median is the TRIMMEAN function.  This function calculates the average, but it enables you to exclude the most extreme values.  For example, if you had 100 values in your dataset and you trimmed 20% of the values, TRIMMEAN would exclude the lowest 10 and the highest 10 values from the average.

In one of my earlier posts, I went through the steps of how to create a pivot table that computes averages for large sets of data.  Unfortunately, pivot tables do not have the functionality to compute the median values.  There is a workaround though! Please stay tuned to this series to learn how it's done. In the meantime, please share your Excel experiences with us... Have you discovered other common data analysis mistakes?

Originally published Apr 26, 2011 9:30:00 AM, updated July 28 2017

Topics:

Excel