I have a set of Google Anlaytics data in a spreadsheet that lists all the pages viewed over a period of time by month. So it looks like;
Month | Page Title | Page Views | Visitors | Bounce Rate
Dec Page1 250 199 20
Dec Page2 240 189 10
Nov Page1 260 192 30
Nov Page2 250 190 10
On a Summary page I have a drop down to select the month for which I want data, there are several bits of data that work of this.
For this particular set of data "Top 5 Popular Pages by Month" I want to select the Month and then return the top 5 pages for that month.
I realise this can be done with a pivot table that has month as filter and a bit of VBA but I so far i have avoided VBA for this spreadsheet and would like to find a way to do it without code, I am happy to use intermediate/helper columns/tables it doesn't have to be a megaformula.
oops I left out the final grab of the page name and didn't metion it's an array function! Thank you HackSlash.
{=INDEX(B:B,MATCH("dec"&LARGE(IF(A:A="dec",C:C),1),A:A&C:C,0),1)}
THIS IS AN ARRAY FUNCTION. You must press cntrl shift enter to make Excel put the brackets around the outside of it to work.
This returns the value of column B where the value of column C is greatest where column A contains "dec". Change the 1 to 2 for second, to 3 for third, etc...
You can use a cell reference instead of "dec" if you want.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments