percent rank and index/match/choose?

[penfold]

Hi,

I have a table which varies in size from A1:B3 to A1:B100. The final row of the table is a "Total" for column B.

I want to add a PERCENTRANK in column C. I have tried writing this with an array from B1:B100 but the problem with this is that it will rank the "Total" which is not what I want.

Is there a formula which will allow me to PERCENTRANK from B1 to the row above "Total" in column A?

Many thanks

[rylo]

Hi

Try creating a dynamic defined name to cover the relevant range.

Insert, Name, Define, name: myrng, refers to: =OFFSET(Sheet1!$B$1,0,0,COUNT(Sheet1!$B:$B)-1,1)

This should cover the range from B1 to the row above the total. Use the defined name in your PERCENTRANK function.

HTH

rylo