| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining an IF and MAX function
ok, say I have this worksheet Gemma Senior 1.1 Mark Senior 1.0 Giles Senior 1.1 Matthew Senior 0.2 Owen Senior 0.7 Kevin Senior 1.8 Alex Senior 0.0 Valentina Senior 1.3 Katie Junior 1.3 Ashlea Junior 0.2 Tara Senior 0.0 Tahlia Sub Junior 0.9 Tyler Sub Junior 1.0 Kylie Junior 1.6 Mark Senior 0.0 first column (C2:C16) is the name of people in my tennis club second row (D2:D16) is their division and third row (J2:J16) is their ongoing score what would the formula be if I wanted to put the leading top scorer for each division (senior, sub junior or senior) into three seperate cells? -- Drummy ------------------------------------------------------------------------ Drummy's Profile: http://www.excelforum.com/member.php...o&userid=34780 View this thread: http://www.excelforum.com/showthread...hreadid=548237 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining an IF and MAX function
Response posted in microsoft.public.excel.misc.
Please don't cross-post. Cheers -- macropod [MVP - Microsoft Word] "Drummy" wrote in message ... ok, say I have this worksheet Gemma Senior 1.1 Mark Senior 1.0 Giles Senior 1.1 Matthew Senior 0.2 Owen Senior 0.7 Kevin Senior 1.8 Alex Senior 0.0 Valentina Senior 1.3 Katie Junior 1.3 Ashlea Junior 0.2 Tara Senior 0.0 Tahlia Sub Junior 0.9 Tyler Sub Junior 1.0 Kylie Junior 1.6 Mark Senior 0.0 first column (C2:C16) is the name of people in my tennis club second row (D2:D16) is their division and third row (J2:J16) is their ongoing score what would the formula be if I wanted to put the leading top scorer for each division (senior, sub junior or senior) into three seperate cells? -- Drummy ------------------------------------------------------------------------ Drummy's Profile: http://www.excelforum.com/member.php...o&userid=34780 View this thread: http://www.excelforum.com/showthread...hreadid=548237 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining an IF and MAX function
I changed the score of Gemma in the Senior division from 1.1 to 1.8 in
order to illustrate the value of the setup that follows... G1: =MAX(IF($D$2:$D$16=G3,$E$2:$E$16)) which is confirmed with control+shift+enter (not with enter) and copied across to I1. G2, copied across to I2: =SUMPRODUCT(--($D$2:$D$16=G$3),--($E$2:$E$16=G$1)) G3:I3 houses: Senior Junior Sub Junior G4: =IF(ROWS(G$4:G4)<=G$2, INDEX($C$2:$C$16, SMALL(IF($E$2:$E$16=G$1, IF($D$2:$D$16=G$3,ROW($E$2:$E$16)-ROW($E$2)+1)), ROWS(G$4:G4))),"") which is confirmed with control+shift+enter (not with enter) then copied across to I4 and down. Drummy wrote: ok, say I have this worksheet Gemma Senior 1.1 Mark Senior 1.0 Giles Senior 1.1 Matthew Senior 0.2 Owen Senior 0.7 Kevin Senior 1.8 Alex Senior 0.0 Valentina Senior 1.3 Katie Junior 1.3 Ashlea Junior 0.2 Tara Senior 0.0 Tahlia Sub Junior 0.9 Tyler Sub Junior 1.0 Kylie Junior 1.6 Mark Senior 0.0 first column (C2:C16) is the name of people in my tennis club second row (D2:D16) is their division and third row (J2:J16) is their ongoing score what would the formula be if I wanted to put the leading top scorer for each division (senior, sub junior or senior) into three seperate cells? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
