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 you 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 right 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!
Inbound Marketing Blog Feed
Dan Tyre 12:18 PM on May 12, 2011
Pivot tables are for NORMAL PEOPLE, they are not that hard!
mj pierce 3:13 PM on May 14, 2011
And...if you want to hum a nifty tune while you do pivot tables, check out this Ball State University mathematical sciences student music video turned in as a class assignment. It rocks! Check it out!
http://blogs.office.com/b/microsoft-excel/archive/2011/05/06/rockin-out-with-pivottables.aspx