Google-sheets – How to compare duration to another duration using IF statements

google sheetsgoogle-sheets-datesworksheet-function

=IF(TIMEVALUE(H2)<=TIMEVALUE("00:01:00.000"), 150,
 TIMEVALUE(H2)<=TIMEVALUE("00:01:30.000"),100, 
 IF(TIMEVALUE(H2)<=TIMEVALUE("00:02:00.000"), 75, 
 IF(TIMEVALUE(H2)<=TIMEVALUE("00:02:30.000"), 50, 
 IF(TIMEVALUE(H2)<=TIMEVALUE("00:03:00.000"), 40, 
 IF(TIMEVALUE(H2)<=TIMEVALUE("00:03:30.000"), 35, 
 IF(TIMEVALUE(H2)<=TIMEVALUE("00:04:00.000"), 30, 
 IF(TIMEVALUE(H2)<=TIMEVALUE("00:04:30.000"), 25, 
 IF(TIMEVALUE(H2)<=TIMEVALUE("00:05:00.000"),25,0)))))))))

=IF(H2<=TIMEVALUE(“00:01:00”),150,
 H2<=TIMEVALUE(“00:01:30”),100,
 IF(H2<=TIMEVALUE(“00:02:00”), 75, 
 IF(H2<=TIMEVALUE(“00:02:30”), 50,
 IF(H2<=TIMEVALUE(“00:03:00”), 40,
 IF(H2<=TIMEVALUE(“00:03:30”), 35,
 IF(H2<=TIMEVALUE(“00:04:00”), 30, 
 IF(H2<=TIMEVALUE(“00:04:30”), 25, 
 IF(H2<=TIMEVALUE(“00:05:00”),25,0)))))))))

Using this and Similar lines code/syntax whatever you'd like to call them, all I get is #Error. What I am trying to do is assign point values to times for a race. So if the racer's time is less than or equal to a minute then they would get 150 points, etc…
What function or combination of functions would I use to successfully do this in Google Sheets?

Best Answer

Walter. Let me suggest an entirely different (and shorter) approach:

=IF((TIMEVALUE(H2)*24*60)>5,0,CHOOSE(CEILING(((TIMEVALUE(H2)-TIMEVALUE("00:00:30"))*24*60),0.5)/0.5,150,100,75,50,40,35,30,25,25,0))

or displayed differently:

=IF( (TIMEVALUE(H2)*24*60)>5,0, CHOOSE( CEILING(((TIMEVALUE(H2)-TIMEVALUE("00:00:30"))*24*60),0.5)/0.5, 150,100,75,50,40,35,30,25,25,0) )

HOW IT WORKS:

First of all, the result of TIMEVALUE must be multiplied by minutes in an hour (60) and hours in a day (24) to give you actual minutes. (Otherwise, it just returns the fraction of a 24-hour day that the given TIMEVALUE is.

The first condition of the IF says "If it was over five minutes, give it a score of zero.

If not, we enter something into a CHOOSE function. CHOOSE allows us to pick a number and then search a line-up of options for whole-number values 1, 2, 3, 4, 5, etc.

We can manipulate the time into a whole-number choice for any time by subtracting 30 seconds and finding out how many 30-second intervals are left in that amount. CEILING allows us to keep all answer marked off by rounding UP to the nearest 0.5.

If you take TIMEVALUE("00:01:00") for instance, we subtract 30 seconds, which brings us down to TIMEVALUE("00:00:30"), then we divide by 0.5 (which is half a minute, or 30 seconds) to find out there is 1 30-second interval left. So the CHOOSE function returns the result in position 1 ... which is 150.

Trying it for TIMEVALUE("00:02:17"), we subtract 30 seconds, which brings us down to TIMEVALUE("00:01:47"). We then divide by 0.5 to find out how many 30-second intervals are in this duration, getting the answer 3.566. CEILING rounds this up to the nearest increment of 0.5, which is 4. CHOOSE returns the result in position 4 — which is 50.

That said, if you did want to stick with your listing format, you'd be better off going with IFS than a bunch of individual nested IF statements. That would look like this:

=IFS( TIMEVALUE(H2)<=TIMEVALUE("00:01:00"),150, TIMEVALUE(H2)<=TIMEVALUE("00:01:30"),100, TIMEVALUE(H2)<=TIMEVALUE("00:02:00"),75, TIMEVALUE(H2)<=TIMEVALUE("00:02:30"),50, TIMEVALUE(H2)<=TIMEVALUE("00:03:00"),40, TIMEVALUE(H2)<=TIMEVALUE("00:03:30"),35, TIMEVALUE(H2)<=TIMEVALUE("00:04:00"),30, TIMEVALUE(H2)<=TIMEVALUE("00:04:30"),25, TIMEVALUE(H2)<=TIMEVALUE("00:05:00"),25, TRUE,0)

Here, the final TRUE just means "for anything else..."

Related Topic