How do you add a dynamic reference in a percentrank formula?

[Zdog]

Hello lovely people of excel forum,

Please can someone help me with this excel problem? I am sure it is an easy fix, but I cannot fathom it. I have added a fake data set to illustrate my point.

I want to percentrank the mean score (column E), per data in column D (category). If there was the same number of rows per category, I think I could solve it; however, the number of rows differs per category. Thus, I need to somehow make the part of the formula that references the specific area in column E dynamic i.e. in the formula below, I need to replace the ‘E$2:E$18’ bit with something that uses column D to define its upper/lower limits.

=(PERCENTRANK(E$2:E$18,E2))*100


Penny for your thoughts, excel gurus.

[JohnTopley]

Try

=(PERCENTRANK(OFFSET(E$2,MATCH($C2,$C$2:$C$100,0)-1,0,COUNTIFS($C$2:$C$100,$C2,$A$2:$A$100,$A2),1),E2))*100

[Zdog]

Sir JohnTopley, you are a hero amongst men - thank you!

Any chance you can explain how it works? Now worries if not, you have already done me a huge favour!

Thank you.

[Roger Govier]

I think that this formula will do it

Please Login or Register  to view this content.

[JohnTopley]

(PERCENTRANK(OFFSET(E$2,MATCH($C2,$C$2:$C$100,0)-1,0,COUNTIFS($C$2:$C$100,$C2,$A$2:$A$100,$A2),1),E2))*100

The OFFSET function is based on E$2 as a fixed starting cell.

The MATCH function finds the FIRST row which matches the INDICATOR (Column C) and we subtract 1 to get the starting row of our required range.

The COUNTIFS counts the number of occurrences of PROGRAM and INDICATOR which gives the number of rows (Height in OFFSET) to be included in our range.


FYI: The COUNTIFS could be changed to COUNTIF($D$2:$D$100,$D2) i.e use your CATEGORY column.



To see this working, select F2==>Formulas=>Evaluate Formula==>Evaluate

Hope this helps.

[Zdog]

Thank you for the other suggestion, Roger Govier. I ended up making a combination of the two suggestions to fit my needs. Excellent.

JohnTopley - thank you for taking the time to break that down for me - very helpful. Much appreciated.