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

Greg S.
Greg S.
edited 05/09/24 in Formulas and Functions

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
    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!