Hi all,

I am trying to determine the top score of rankings in three different columns Q, R and S
Each column represents a category (Ms, Mil and Mace)

Here is the formula for Column Q
=IF(A4="Ms",SUMPRODUCT(--(A$4:A$123="Ms"),--(O4<O$4:O$123))+1-COUNTIFS($O$4:$O$122,LEFT($G$123,FIND(" ",$G$123))+0,$B$4:$B$122,MID($G$123,FIND(" ",$G$123)+1,255)),"")

Here is the formula for Column R


=IF(A4="MIL",SUMPRODUCT(--(A$4:A$123="MIL"),--(O4<O$4:O$123))+1-COUNTIFS($O$4:$O$122,LEFT($G$123,FIND(" ",$G$123))+0,$B$4:$B$122,MID($G$123,FIND(" ",$G$123)+1,255)),"")
Here is the formula for Column S

=IF(A4="Mace",SUMPRODUCT(--(A$4:A$123="Mace"),--(O4<O$4:O$123))+1-COUNTIFS($O$4:$O$122,LEFT($G$123,FIND(" ",$G$123))+0,$B$4:$B$122,MID($G$123,FIND(" ",$G$123)+1,255)),"")

G123 is the formula for the Max score
={TEXT(MAX(IF(X4:X122<>"Exh",O4:O122)),"0.0")&" "&INDEX(B4:B122,MATCH(1,((X4:X122<>"Exh")*(O4:O122=MAX(IF(X4:X122<>"Exh",O4:O122)))),0))}
The problem is that every column now ranks from 0 through whatever. I want to leave out the high score from the column where it occurs and then have the other columns list from 1 through whatever.

Any ideas?

Thanks for any help!