Search code examples
excelexcel-formulaxlsx

Excel shows name twice

I got a problem with my Excel, where it shows a same name twice, if the 'racers' have the excact same time. For example in the picture the racers 7 & 9 and racers 5 & 10 have the same time, but in the Start grid it shows the same name twice. It should be 4. Racer7 5. Racer9 & 9. Racer5 10. Racer10

enter image description here

The Function of Cell I3 =IF(OR(ISBLANK(B3);ISBLANK(C3));"";INDEX($B$3:$B$32;MATCH(J3;$D$3:$D$32;0))) (I have to use format ';' instead of ',') Function of cell J3 =IFERROR(SMALL($D$3:$D$32;H3);"")

Link to the file (does not work in Google Sheets & the functions have to use local formatting)

xlsx file

Solution

  • MATCH and RANK Getting Sick When Handling Time

    Formulas

    COMMA

    [D3]    =IF(NOT(ISNUMBER(C3)),"",ROUND($D$1-A3*"00:10,0"-C3,8))
[H3]    =IF(ISNUMBER(K3),RANK(J3,D$3:D$32,2),"")
[I3]    =IF(IFERROR(INDEX($B$3:$B$32,SMALL(IF($D$3:$D$32=J3,ROW($J$3:$J$32)-ROW(J$3)+1),COUNTIF($J$3:$J3,J3))),"")=0,"",IFERROR(INDEX($B$3:$B$32,SMALL(IF($D$3:$D$32=J3,ROW($J$3:$J$32)-ROW(J$3)+1),COUNTIF($J$3:$J3,J3))),""))
[J3]    =IFERROR(SMALL($D$3:$D$32,A3),"")
[K3]    =IFERROR(J3-J$3,IF(I3="","","disqualified"))

    enter image description here

    COLON

    [D3]    =IF(NOT(ISNUMBER(C3));"";ROUND($D$1-A3*"00:10;0"-C3;8))
[H3]    =IF(ISNUMBER(K3);RANK(J3;D$3:D$32;2);"")
[I3]    =IF(IFERROR(INDEX($B$3:$B$32;SMALL(IF($D$3:$D$32=J3;ROW($J$3:$J$32)-ROW(J$3)+1);COUNTIF($J$3:$J3;J3)));"")=0;"";IFERROR(INDEX($B$3:$B$32;SMALL(IF($D$3:$D$32=J3;ROW($J$3:$J$32)-ROW(J$3)+1);COUNTIF($J$3:$J3;J3)));""))
[J3]    =IFERROR(SMALL($D$3:$D$32;A3);"")
[K3]    =IFERROR(J3-J$3;IF(I3="";"";"disqualified"))

    Why is MATCH 'miscalculating' to '7' instead of '6' in cells 'I6' and 'I7' in OP's worksheet (formula in 'D3')?

    Time has a ton of decimals so I guess it's 'seeing' the values in 'D8' and 'D9' as different values. To avoid this you can round the values. If you want to use only these values it is enough to round them to 8 decimals for the numbers to be recognized as different even by a millisecond. If you want to sum them there might be some inaccuracies. In OP's case 8 decimals is more than enough.

    RANK (formula in 'H3') is also 'miscalculating' if no rounding.

    Why the long formula?

    Best try it with and without the IF statement and see for yourself.

    Here's a Hint:

    enter image description here