How to return a value based on # value or a null value in a range. The # of miles to which zone

Greg S.

Hi All,

I have found lots of helpful pieces of information from this forum but I could not locate a previous example similar to this one, My hope is someone else has the same issue too.

I am trying to write a formula that returns a value based on the value in another column. In this example the amount of mileage dictates which hot route zone the location is in.

Here are the value ranges to help:

• 599 miles or more returns "No HR" in the hot route zone column
• 101 - 599 miles returns "CALL" in the hot route zone column
• 75- 100 miles returns "Zone3" in the hot route zone column
• 50- 74 miles returns "Zone2" in the hot route zone column
• 49 miles or less returns "Zone1" in the hot route zone column
• If the cell is blank then it returns "CALL" in the hot route zone column

I was able to most of it to work except the null pull being to call (if the cell is blank then it should return "CALL"

So just to recap and to provide some examples - here is how the formula should work:

• If the cell has the value of 602 in the mileage column then the formula returns "No HR"
• If the cell has a value of 155 then the formula should return "CALL"
• If the cell has a value of blank (null) then the formula should return "CALL"
• If the cell has a value of 83 then the formula should return "Zone3"
• If the cell has a value of 57 then the formula should return "Zone2"
• If the cell has a value of 36 then the formula should return "Zone1"

I have not added the logic for handling blanks (should return "CALL") but currently it is throwing an error for not par sable for this and I can not figure it out. Please let me know how to fix and/or if there is a better way to write this based on above requirements.

Smartsheet Formula:

=IF([Hot Route Mileage (Roundtrip)]@row)>=600, "No HR",IF(AND([Hot Route Mileage (Roundtrip)]@row<=599,[Hot Route Mileage (Roundtrip)]@row>=101),"CALL", IF(AND([Hot Route Mileage (Roundtrip)]@row<=100,[Hot Route Mileage (Roundtrip)]@row>=75),"Zone3",IF(AND([Hot Route Mileage (Roundtrip)]@row<=74,[Hot Route Mileage (Roundtrip)]@row>=50),"Zone2",IF([Hot Route Mileage (Roundtrip)]@row<50,"Zone1"))))

Greg S.

Thank you! That solves my first mistake, now the next part is adding in the if it's blank than return "CALL". Where do I add that into this formula without eliminating the old functionality?

It is currently treating the blank field as a 0 which it thinks is 0 miles or zone1 when it should be saying Call. I have updated it to treat the blank column like a 0 and that fixed it. Below is the final formula that I used to fix it. Thank you for your help!

Updated Formula since last change:

=IF([Hot Route Mileage (Roundtrip)]@row >= 600, "No HR", IF(AND([Hot Route Mileage (Roundtrip)]@row <= 599, [Hot Route Mileage (Roundtrip)]@row >= 101), "CALL", IF(AND([Hot Route Mileage (Roundtrip)]@row <= 100, [Hot Route Mileage (Roundtrip)]@row >= 75), "Zone3", IF(AND([Hot Route Mileage (Roundtrip)]@row <= 74, [Hot Route Mileage (Roundtrip)]@row >= 50), "Zone2", IF(AND([Hot Route Mileage (Roundtrip)]@row < 50, [Hot Route Mileage (Roundtrip)]@row >= 1), "Zone1", IF([Hot Route Mileage (Roundtrip)]@row = 0, "CALL"))))))

bisaacs

Hey @Greg S.,

It appears you closed off the IF statement too early on the first logical expression:

=IF([Hot Route Mileage (Roundtrip)]@row)>=600, "No HR",IF(AND([H….

Try removing that (as well as double check the syntax in the rest of the formula)! If a formula throws an #UNPARSEABLE error, that usually means there's something wrong with the formula syntax, rather than the logic of the formula.

Greg S.

Thank you! That solves my first mistake, now the next part is adding in the if it's blank than return "CALL". Where do I add that into this formula without eliminating the old functionality?

It is currently treating the blank field as a 0 which it thinks is 0 miles or zone1 when it should be saying Call. I have updated it to treat the blank column like a 0 and that fixed it. Below is the final formula that I used to fix it. Thank you for your help!

Updated Formula since last change:

=IF([Hot Route Mileage (Roundtrip)]@row >= 600, "No HR", IF(AND([Hot Route Mileage (Roundtrip)]@row <= 599, [Hot Route Mileage (Roundtrip)]@row >= 101), "CALL", IF(AND([Hot Route Mileage (Roundtrip)]@row <= 100, [Hot Route Mileage (Roundtrip)]@row >= 75), "Zone3", IF(AND([Hot Route Mileage (Roundtrip)]@row <= 74, [Hot Route Mileage (Roundtrip)]@row >= 50), "Zone2", IF(AND([Hot Route Mileage (Roundtrip)]@row < 50, [Hot Route Mileage (Roundtrip)]@row >= 1), "Zone1", IF([Hot Route Mileage (Roundtrip)]@row = 0, "CALL"))))))