how do i use the RANK formula within a pivot table??

[perrymagic]

hello

i am a regular user of pivot tables however i wish to be able to insert a calculated field which has the RANK formula in it.. is this possible?

i have tried formulas outside the pivot table which relate to the data within the pivot table but i get rather confused as to the formula as it has "GET PIVOT TABLE DATA" etc etc in the formula bar.

any help is greatly appreciated,.

thanks
matt

[StephenR]

To get rid of that annoying GETPIVOTDATA business, see here:http://www.contextures.com/xlPivot06.html

[perrymagic]

thanks very much for the advice on annoying "get pivot table" problem.

[Palmetto]

To answer your question as stated in your thread title, you cannot use the RANK function directly in the PT, such as in a calculated field. You will have to use the GetPivotData method.

If possible with your data, you could rank the source data and and then create the Pivot Table so that you can sort, filter, etc as needed.

[StephenR]

Why can't you just use

formulas outside the pivot table which relate to the data within the pivot table

?

[perrymagic]

thanks for all your replies however i really need the rank formulas to update when i filter/edit the pivot table. therefore i think i will have to resign to the fact that i cant do it. Its also not possible to rank my source data due to the nature of it. thanks anyway, much appreciated,.

[StephenR]

If you want, attach a small sample workbook illustrating what you want to do, and one of us can take a look and perhaps offer a constructive suggestion or two.