ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Rank function with Pivot (https://www.excelbanter.com/excel-worksheet-functions/198619-rank-function-pivot.html)

Roger Halbheer

Rank function with Pivot
 
Hi All,
I am using Pivot tables on top of an Access DB. What I would like to do is a
calculated column using something like the RANK function wihtin the Pivot.
I have not found a way using this. Is this possible?
Thank you
Roger

ryguy7272

Rank function with Pivot
 
I'm not sure what's you've got there, but this is what I have done for a
project recently.
PT data is in A3:B12; numbers are in B5:B11 (I'm not including the Grand
Total in B12).

In C5:C11, put 1, 10, 20, 30, 40 50, 60.

In D5, copy/paste this function:
=FREQUENCY($B$5:$B$11,C5:C11)

Click on D5, and select down to D11 (not a fill-down).

With that range selected, click back in the formula bar, and hit ctrl +
shift + enter...all at the same time.

Than you can build fancy little charts and all kinds of fun stuff.

Does that help?


Regards,
Ryan---

--
RyGuy


"Roger Halbheer" wrote:

Hi All,
I am using Pivot tables on top of an Access DB. What I would like to do is a
calculated column using something like the RANK function wihtin the Pivot.
I have not found a way using this. Is this possible?
Thank you
Roger


Roger Halbheer[_2_]

Rank function with Pivot
 
Hi Ryan,
thank you for your feedback. However, it does not solve my problem. What I
would like to do is not counting the frequency of the values but adding a
column with the ranking of the values. So, adding a figure as I can do in
Excel in "normal" tables with the RANK function
Roger

"ryguy7272" wrote:

I'm not sure what's you've got there, but this is what I have done for a
project recently.
PT data is in A3:B12; numbers are in B5:B11 (I'm not including the Grand
Total in B12).

In C5:C11, put 1, 10, 20, 30, 40 50, 60.

In D5, copy/paste this function:
=FREQUENCY($B$5:$B$11,C5:C11)

Click on D5, and select down to D11 (not a fill-down).

With that range selected, click back in the formula bar, and hit ctrl +
shift + enter...all at the same time.

Than you can build fancy little charts and all kinds of fun stuff.

Does that help?


Regards,
Ryan---

--
RyGuy


"Roger Halbheer" wrote:

Hi All,
I am using Pivot tables on top of an Access DB. What I would like to do is a
calculated column using something like the RANK function wihtin the Pivot.
I have not found a way using this. Is this possible?
Thank you
Roger


Herbert Seidenberg

Rank function with Pivot
 
This duplicates the RANK function for a Pivot Table.
Two helper columns are added to the source code.
File at:
http://www.savefile.com/files/1727795


All times are GMT +1. The time now is 10:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com