Search code examples
excelexcel-formulaconditional-statementsmax

get best round score ( max score and min second) in excel

I have a sheet that include 7 columns: ( name , score1 , time1, score2 , time2 , score3 , time3).
I want to select best round score provided that first find best score( max score), if there are 2 rounds include max score , then select the rounds that include best time ( min time).

like below table ( get final score and final time):

name score1 time1 score2 time2 score3 time3 final_score final_time
name 1 100 20 90 10 100 19 100 19
name 2 10 40 50 15 25 2 50 15

In max formula we have one condition and I don't know how can I do this...

Solution

  • Welcome to SO.

    Best Score it's easy because it will always be the max score of the corresponding columns. The tricky part is the time associated to a specific max score. I've used the headers value to locate the time columns based on your input

    enter image description here

    Formula for best score is: =MAX(B2;D2;F2)

    Formula for best time is: =MIN(IF(B2:F2=$I2;IF(LEFT($C$1:$G$1;4)="time";C2:G2)))

    Just drag down.

    NOTE: The formula for best time is an array formula so it must be entered using ENTER+CTRL+SHIFT or it won't work!

    Also, notice my argument separator is ; so you may need adapt that part