Formula is not returning the correct value in 2 scenarios - 75 & 101 based on data range
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!
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!