If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Extract Top 5 value from a pivot table
Hello,
I have a a pivot table listing by country and months of the number of times a certain internet page has been reviewed. I would like to have a formula (x5) which would extract the 5 highest pages viewed for a given month and given country. I tried the Getpivotdata and sumproduct functions, but I am getting nowhere. Can you help? Thank you |
#2
|
|||
|
|||
Extract Top 5 value from a pivot table
Could you provide a sample of your data?
On 23 Kwi, 01:47, Andre C Andre wrote: Hello, I have a a pivot table listing by country and months of the number of times a certain internet page has been reviewed. I would like to have a formula (x5) which would extract the 5 highest pages viewed for a given month and given country. I tried the Getpivotdata and sumproduct functions, but I am getting nowhere. Can you help? Thank you |
#3
|
|||
|
|||
Extract Top 5 value from a pivot table
Hello Here is example of data: Dec 09 Jan 10 Feb 10 USA Page 18 56 75 100 Page 24 15 85 60 Page 3 67 18 40 Page 41 23 66 80 Page 52 11 89 30 Canada Page 13 56 75 100 Page 21 89 54 18 Page 32 67 23 78 Page 4 34 95 60 Page 5 67 83 31 Page 7 23 45 21 For the example above, I would like to have a formula where I would select these Input cells : USA Dec09 formula 1 & 2: highest number would have result PAGE 3 and 67 formula 3 & 4 second highest would have result PAGE 18 and 56 formula 5 &6 third highest would have resuly Page 41 and 23 If I was to change the input cell to CANADA and Jan 10 I would want to have these results (separate cells) Page 4 95 Page 5 83 Page 13 75 I do have thousands of rows of data (can vary), and I need the page number and the value of the say the 3 biggest values for an individual country and particular month Hope this makes it more clear Thank you Andre "Andre C" wrote: Hello, I have a a pivot table listing by country and months of the number of times a certain internet page has been reviewed. I would like to have a formula (x5) which would extract the 5 highest pages viewed for a given month and given country. I tried the Getpivotdata and sumproduct functions, but I am getting nowhere. Can you help? Thank you |
#4
|
|||
|
|||
Extract Top 5 value from a pivot table
Excel 2007 PivotTable
Top 3 No code, no formulas. http://c0718892.cdn.cloudfiles.racks.../04_23_10.xlsx Pdf preview: http://www.mediafire.com/file/zz2zj3zyznj/04_23_10.pdf |
#5
|
|||
|
|||
Extract Top 5 value from a pivot table
Hello Herbert,
Thank you for your file. I learned more about the 2007 pivot table. However, I need a report that will give me the top 3 pages for a series of months. The type of reports i am looking for (based on your example) is TOP PAGE Dec Jan Feb (Reference) Greece 11 14 11 Hungary 26 27 26 Iceland 39 38 40 etc Next to that, a report for the corresponding value TOP PAGE Dec Jan Feb etc (Value) Greece 444 313 290 Hungary 115 136 110 Iceland 430 230 461 etc And finally repeat the report for 2nd and 3rd highest page. I could see using your pivot table, but I imagine having to create as many pivot tables as I have different months. Thanks for your help André "Herbert Seidenberg" wrote: Excel 2007 PivotTable Top 3 No code, no formulas. http://c0718892.cdn.cloudfiles.racks.../04_23_10.xlsx Pdf preview: http://www.mediafire.com/file/zz2zj3zyznj/04_23_10.pdf . |
#6
|
|||
|
|||
Extract Top 5 value from a pivot table
Excel 2007 PivotTable
Top One No code, no formulas. Other configurations w/o PTs, but monster formulas, on request. http://c0718892.cdn.cloudfiles.racks.../04_23_10.xlsx Pdf preview: http://www.mediafire.com/file/xukndqm3nho/04_23_10.pdf |
#7
|
|||
|
|||
Extract Top 5 value from a pivot table
Simply amazing!!!
Thank you for taking the time to help, you have gone over and above my expectations! André a French Canadian living in Australia "Herbert Seidenberg" wrote: Excel 2007 PivotTable Top One No code, no formulas. Other configurations w/o PTs, but monster formulas, on request. http://c0718892.cdn.cloudfiles.racks.../04_23_10.xlsx Pdf preview: http://www.mediafire.com/file/xukndqm3nho/04_23_10.pdf . |
Thread Tools | |
Display Modes | |
|
|