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

1

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

0

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)))))))))

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.