Formula is not returning the correct value in 2 scenarios - 75 & 101 based on data range

edited 05/09/24

Here is the 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(AND([Hot Route Mileage (Roundtrip)]@row < 50, [Hot Route Mileage (Roundtrip)]@row >= 1), "Zone1", IF([Hot Route Mileage (Roundtrip)]@row = 0, "CALL"))))))

This works for all fields except when 101 is entered, it returns a blank value and when it should come back as "CALL".

A similar issue occurs for any records where the mileage is = 75 and it should return a value of Zone3.

how do I modify the formula to enable the correct value to be returned for 101 and 75? I am very close to getting this right!

• ✭✭✭✭✭✭

Hello @Greg S.

When I test your formula, without any corrections, I receive a CALL for 101 and Zone3 for 75. Paste your formula back into your sheet and try again. Please let me know if you still receive unexpected results.

Kelly

• Hi Kelley, The problem is Smartsheet returns a blank for those two values (101 or 75). I am stuck on how to correct that. I think I am really close to getting these two values to work correctly. I also added a screenshot for how it is working for the other values. Is there anything I need to change on the formula?

Here is the formula currently for the column:

=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!