IF Statements when a score / number is between two values
Comments
-
@Desirae Moore Try moving that last closing parenthesis to before the +.
-
@Paul Newcome it still shows as incorrect
=IF([time conversion]@row < 7, "7AM", IF([time conversion]@row < 8, "8AM", IF([time conversion]@row < 9, "9AM", IF([time conversion]@row < 10, "10AM", IF([time conversion]@row < 11, "11AM", IF([time conversion]@row < 12, "12PM", IF([time conversion]@row < 13, "1:30PM", IF([time conversion]@row < 14, "2:30PM", IF([time conversion]@row < 15, "4PM", IF([time conversion]@row, <16, "5:30PM", IF([time conversion]@row < 17, "5:30PM", IF([time conversion]@row < 18, "7:30PM", IF([time conversion]@row < 19, "7:30PM", IF([time conversion]@row, <20, "8:30PM"))))))))))))) + "TRUCK")
-
@Desirae Moore The closing parenthesis is still there after "TRUCK". That's the one we need to move. See below:
=IF([time conversion]@row < 7, "7AM", IF([time conversion]@row < 8, "8AM", IF([time conversion]@row < 9, "9AM", IF([time conversion]@row < 10, "10AM", IF([time conversion]@row < 11, "11AM", IF([time conversion]@row < 12, "12PM", IF([time conversion]@row < 13, "1:30PM", IF([time conversion]@row < 14, "2:30PM", IF([time conversion]@row < 15, "4PM", IF([time conversion]@row, <16, "5:30PM", IF([time conversion]@row < 17, "5:30PM", IF([time conversion]@row < 18, "7:30PM", IF([time conversion]@row < 19, "7:30PM", IF([time conversion]@row, <20, "8:30PM")))))))))))))) + " TRUCK"
-
@Paul Newcome It seems like once it goes to 15 and after it doesn't work for some reason. It works all the way up until 14 and then after that, it stops.
-
-
@Desirae Moore What happens if you also put in a few test entries for the other times beyond the 15?
-
-
@Desirae Moore I am not sure what's going on here. I have looked through that formula a million times and can't see anything wrong with the syntax or anything.
Try this... Delete the formula completely from the sheet. Log out. Clear your browser's cookies and cache. Log in. Manually (and very carefully) retype the formula.
@Genevieve P. I am hoping that maybe I have just stared at it for too long too many times and am missing something simple. Would you mind providing a fresh set of eyes on this one?
-
👀
@Paul Newcome It's a comma! Or 2, to be exact.
@Desirae Moore, there should be no commas between [time conversion]@row and <.
However there is a comma before 16:
IF([time conversion]@row, <16, "5:30PM",
and before 20:
IF([time conversion]@row < 20, "5:30PM",
Remove those two and it should work.
=IF([time conversion]@row < 7, "7AM", IF([time conversion]@row < 8, "8AM", IF([time conversion]@row < 9, "9AM", IF([time conversion]@row < 10, "10AM", IF([time conversion]@row < 11, "11AM", IF([time conversion]@row < 12, "12PM", IF([time conversion]@row < 13, "1:30PM", IF([time conversion]@row < 14, "2:30PM", IF([time conversion]@row < 15, "4PM", IF([time conversion]@row <16, "5:30PM", IF([time conversion]@row < 17, "5:30PM", IF([time conversion]@row < 18, "7:30PM", IF([time conversion]@row < 19, "7:30PM", IF([time conversion]@row <20, "8:30PM")))))))))))))) + " TRUCK"
Cheers!
Genevieve
-
@Genevieve P. Ugh. 🤦♂️ Of course now that you point them out they are glaringly obvious. Thank you for the fresh set of eyes!
-
Haha no problem at all 🙂
-
@Genevieve P. THANK YOU!!!! I swear I looked at this formula and rewrote it at least 25 times and I could not figure out what I was doing wrong lol. @Paul Newcome As always you're amazing! lol
-
Can you help with my formula please?
I'm trying to update a column with either 1, 2 or 3 depending on the spend
=IF(Spend@row = <10000, 1, IF(Spend@row < 20000, 2, IF(Spend@row <100000, 3, "")))
I get #INVALID OPERATION
Many thanks in advance.
-
@shamze Try removing the second =.
-
Hello There:
I am struggling with the following related formula:
Goal:
- If a cell value falls between 0-4, the next cell value be LOW
- If a cell value falls between 5-9, the next cell value be MED
- If a cell value falls between 10-20, the next cell value be HIGH
- If a cell value is over 20, the next cell value be CRITICAL
Any help would be absolutely appreciated.
