Indicating the first occurance of the max value in a range

[Aline Yiu]

What to do to indicate the first highest score for each person?


Here's the example:

ID score score used
a 1
a 2 2
b 3
b 2
b 1
b 4 4
b 4
c 2 2
d 2 2
d 2
e 1 1
e 1

I have a formula (thanks to the Excel expert) that indicates all the max
values in a range: =IF(MAX(IF($A$2:$A13=A2, $B$2:$B13)), B2, " "), but I'd
like to have it to show right away the first occurance of the max value.

Thank you for your help.

 

[Max]

One way

With data assumed in A2:B13

Put in C2 ("Score used" col), then array-enter the formula
by pressing CTRL+SHIFT+ENTER:
=IF(AND(MAX(IF($A$2:$A$13=A2,$B$2:$B$13))=B2,SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))<2),B2,"")
Copy C2 down to C13