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

Greg S.

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!

Kelly Moore

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

Greg S.

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