Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
index?lookup?match?if?
I am making a spreadsheet to record compeditive times in an autocross. Colums a,b,and c, contain respectively driver number, driver name and car number. More than one driver can drive the same car. Colums d,e,f,and g contain additional data. column H contains imput of each driver's time. I have used SMALL(h2:h39,1) and SMALL(h2:h39,2) etc. to determine the fastest times in order. This gives me the fastest times in order, Then below that, use MATCH( cell where "small" is,a2:b39,0etc) to match driver number and name to fastest time. I have the problem of a tie in time. Small picks the tieing times and records them but "small" and "match" only go back to the first driver number/name. I want the second member of the tie to show up in second location. I'm trying to avoid a macro for simplicity sake. Please help. Thanks -- rduffey ------------------------------------------------------------------------ rduffey's Profile: http://www.excelforum.com/member.php...o&userid=32997 View this thread: http://www.excelforum.com/showthread...hreadid=528215 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
index?lookup?match?if?
Assuming that A2:H39 contains the data, try the following...
I2, copied down: =RANK(H2,$H$2:$H$39,1)+COUNTIF($H$2:H2,H2)-1 J1: =MAX(I2:I39) K2, copied down and to Column L: =IF(ROWS(K$2:K2)<=$J$1,INDEX(A$2:A$39,MATCH(ROWS(K $2:K2),$I$2:$I$39,0))," ") M2, copied down: =IF(ROWS(M$2:M2)<=$J$1,INDEX(H$2:H$39,MATCH(ROWS(M $2:M2),$I$2:$I$39,0))," ") Hope this helps! In article , rduffey wrote: I am making a spreadsheet to record compeditive times in an autocross. Colums a,b,and c, contain respectively driver number, driver name and car number. More than one driver can drive the same car. Colums d,e,f,and g contain additional data. column H contains imput of each driver's time. I have used SMALL(h2:h39,1) and SMALL(h2:h39,2) etc. to determine the fastest times in order. This gives me the fastest times in order, Then below that, use MATCH( cell where "small" is,a2:b39,0etc) to match driver number and name to fastest time. I have the problem of a tie in time. Small picks the tieing times and records them but "small" and "match" only go back to the first driver number/name. I want the second member of the tie to show up in second location. I'm trying to avoid a macro for simplicity sake. Please help. Thanks |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
index?lookup?match?if?
Thanks, Domenic. Looks like your solution will work great for me. Now I've got some expanding and cleaning to do. -- rduffey ------------------------------------------------------------------------ rduffey's Profile: http://www.excelforum.com/member.php...o&userid=32997 View this thread: http://www.excelforum.com/showthread...hreadid=528215 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|