How to add Rank to Pivot Table

**bonny24tycoon** · 05-15-2012, 12:39 PM

Hi Team,

I would need help in creating a formulated rank criteria in the pivot table so thats the pivot reflects the rank of the options for each team.

I have attached the example sheet to explain this better. PE List-test.xlsx

I have the raw data on "Data" tab.

Thanks,

Bonny Tycoon

**GeneralDisarray** · 05-15-2012, 12:55 PM

are you looking for an average? what do you mean by rank exactly (i assume each line is someones ranking of importance from 1-6 on all categories...)

Take a look at the attachment - for example purposes i'm showing average by dept (assuming the SAP id is tied to the employees). If you click on the + sign for each row label you can see the details for each Sap ID in that department (so like what each employee answered) when you collapse it you see the average of the ranks given for that department...

---
edit
---
to make the example clear, i altered the SAP ID column, i may have misinterpreted what you use that for (i assumed an employee id or something) you can also have used Name in the pivot table where i used SAP id to get the same "dept average - details upon expansion" effect.

Sorry - posted the wrong book at first

**bonny24tycoon** · 05-15-2012, 01:17 PM

Hi There,

Thanks for your reponse.

Im looking at ranking instead of average here. =RANK(number,ref,order)

**micope21** · 05-15-2012, 07:21 PM

Hi

Try this

Cell B6 =RANK(B6,$B6:$G6) Then Cross and Down

**bonny24tycoon** · 05-16-2012, 04:24 AM

Hi micope,

Thanks for your reply.. Im aware of the formula but however, would like it to be built in the pivot itself. (if its possible?)

Thanks,

Bonny Tycoon