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

Options

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

Best Answer

  • Greg S.
    Greg S.
    Answer ✓
    Options

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

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    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.

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Greg S.
    Greg S.
    Answer ✓
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!