Excel 2007: Pivottable and Rank

[peter]

Is there a way to include a ranking within a Pivottable in Excel 2007?

I have used =RANK(H6,H$6:H$77) used this outside of the table for now, so I
do get the correct number, but if I collapse the table, of course I get #N/A.
If the data is updated and I have more rows, then I will need to update the
formula.

I can at least sort them to see which was the best and which was the worst,
but not able to see (without manually coutning) which is #10, #15, etc.

Any help would be greatly appreciated and I will use RANK forumal in the
meantime.

 

[Herbert Seidenberg]

I assume you have a column of numbers, named MyData,
that you want to put into the ROW of a Pivot Table and
you want the DATA to show the original RANK of MyData
inside the Pivot Table.
In that case, add a column to MyData,
named MyOrder with this array formula
=RANK(MyData,MyData)/COUNTIF(MyData,MyData)
In DATA, show Sum of MyOrder.

In Automatic, PV sorts the ROW.
The RANK of sorted, unique MyData is simply {1;2;3;....}

 

[peter]

I very rarely name the data I use as I have never seen a benefit for the use
of it and problem not using it, but maybe I need it here. I have 7 columns
and 44,410 rows of data. Columns 3 & 4 are the only two with data, the rest
are just breakdowns on type or area. When I pivot the data, it narrows down
to about 70 rows as many of it has summed up. This is where I then want to
add the rank.

I did name the data MyData (highlighted it and typed it in the name box) and
then tried to add the array formula and maybe because so many rows, it seemed
to lock up and never completed. I cancelled it.

I will try again, but also wanted to check. If I do set this up in the data
and then sum everything up in Pivottable, will the rank reflect 1 - 70 (or
so) that end up in the table or a sum of their rank within the 40,000+ rows
of data?

 

[Herbert Seidenberg]

If you want the rank within the filtered data (70 or so)
use this formula in MyOrder:
=(SUMPRODUCT((MyData>MyData 2:2)*
(1/COUNTIF(MyData,MyData)))+1)/COUNTIF(MyData,MyData 2:2)
This assumes MyData is a single column, starting at row 2.
Copy the formula down as far as needed. This is not an array formula.
I suggest you troubleshoot the problem/procedure with a very short
and simplified list that fits on one page.
When you name a range, verify it by clicking on the appropriate
name in the name box or go to
Insert > Name > Define
You don't have to use names, it just makes the formula
more readable and it's appearance location independent.