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..."
H2value 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 at 4:32