Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indicating the first occurance of the max value in a range
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. -- Aline Yiu |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indicating the first occurance of the max value in a range
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,SUMPR ODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))<2),B2,"") Copy C2 down to C13 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Aline Yiu" wrote in message ... 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. -- Aline Yiu |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells indicating range of a table | Excel Discussion (Misc queries) | |||
Cells indicating range of a table | Excel Discussion (Misc queries) | |||
Cells indicating range of a table | Excel Discussion (Misc queries) | |||
Find last occurance of text in range | Excel Worksheet Functions | |||
occurance of numbers in cell range | Excel Worksheet Functions |