.
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.
In one of my earlier posts, I walked through the steps of how to create a pivot table that calculates averages for large datasets. Unfortunately, pivot tables do not have the functionality to compute median values. Luckily, there is a workaround! You can create what is effectively a pivot table of medians by using a powerful Excel feature called the array. (Technically, it is not a pivot table, but it will give you the same results.)
I will take through the steps here, but you can also download this spreadsheet if you want to dig in more. In the example below, we have a list of customers along with their company size and their sales cycle length. The two tables (median vs. average) look at the typical sales cycle length according to company size.
The "average" table was created with a pivot table. The "median" table was created by doing the following:
1) Create a column with the six possible "employees" options: 1 to 5, 6 to 10, 11 to 15, etc.
2) In the cell to the left of the "1 to 5" value, type the following: =MEDIAN(IF($A$2:$A$500=D3, $B$2:$B$500))
Here is a breakdown of what those inputs mean:
Translation: First, the IF statement finds all rows where the # of Employees = "1 to 5"; it then stores all of the corresponding "sales cycle" values in an array. The MEDIAN function then pulls the median out of that array of sales cycles for the "1 to 5" customers.
3) There's just one more key ingredient needed to make this formula work. You need to press CTRL + SHIFT + ENTER while you are still active in the cell. This is how you tell Excel that you want it to create an array. Once you do this, something magical happens... you will see curly brackets {} appear around your formula. (Unfortunately, if you type in the curly brackets yourself, Excel won't understand what they mean.)
Features like this give you lots of flexibility for doing any type of marketing analysis. Give it a try and share your thoughts!