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!
Bookmarks