# How to compare duration to another duration using IF statements

``````=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?

• What are the `H2` value shown on the formula bar? Is there a red triangle shown on the cell's top-right corner and in such case, what is the textual error description? – Rubén Feb 4 '19 at 4:32

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..."

Both formulas you provided are correct, however, both of them are broken, because you miss `IF` statement on 2nd line. Also, in your 2nd formula, you use `“ ”` instead of `" "`. Corrected ones:

``````=IF(TIMEVALUE(H2)<=TIMEVALUE("00:01:00.000"), 150,
IF(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,
IF(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)))))))))
``````