IF Statements when a score / number is between two values

Paul Newcome · 01/19/22

@Desirae Moore Try moving that last closing parenthesis to before the +.

Desirae Moore · 01/19/22

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

Paul Newcome · 01/19/22

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

Desirae Moore · 01/25/22

@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 · 01/25/22

@Paul Newcome here's the formula. I just copied yours.

Paul Newcome · 01/25/22

@Desirae Moore What happens if you also put in a few test entries for the other times beyond the 15?

Desirae Moore · 01/25/22

@Paul Newcome the same thing. Please see below.

Paul Newcome · 01/26/22

@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?

Genevieve P. · 01/26/22

👀

@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

Paul Newcome · 01/26/22

@Genevieve P. Ugh. 🤦‍♂️ Of course now that you point them out they are glaringly obvious. Thank you for the fresh set of eyes!

Genevieve P. · 01/26/22

Haha no problem at all 🙂

Desirae Moore · 01/26/22

@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

shamze · 06/15/22

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.

Paul Newcome · 06/15/22

@shamze Try removing the second =.

Pedro Robles · 05/15/23

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.

IF Statements when a score / number is between two values

Comments

Categories