PercentRank Dependent on 2nd Column In Case of a Tie 1

[DeLaMartre]

I would like to have a PercentRank wksht set up whereby if a value in the array is equal to one or more other values, the "tie-breaker" would depend on the value in an adjacent column.

Col A Col B Rank
119 81 100%
118 79 88%
115 71 25%
115 75 25%
115 68 25%
115 72 25%
115 73 25%
114 72 13%
112 65 0%

I am at my wits end on this one. Any help would be, as always, greatly appreciated.

Thanks very much,



-Bob in California

 

[Molby]

Can't you just sort the data using two criteria? The first being the values you are sorting, the second being the values you want to use in the case of the tie break.

 

[DeLaMartre]

Thanks for the reply, Molby. Unfortunately, in the other calculations in the worksheet, a percentrank value is needed. Any other ideas?

Thanks,

-Bob in California

 

[GlennUK]

You could have an extra column, and add column A to column B/100, and use this column as the array and figures to use for the PERCENTRANK.

Or you could use this array formula instead :

=PERCENTRANK($A$1:$A$9+($B$1:$B$9/100),A1+B1/100)

entered with Ctrl-Shift-Enter instead of Enter, in row 1, and then copied down.

Cheers, Glenn.

 

[DeLaMartre]

Thanks so much, Glenn, for the sage advice. This, of course, works perfectly! (You have raised me out of my Excel fog...).

Your time and expertise is very much appreciated - A star for you.

Surf's Up!

-Bob in California