Combined IF AND formula with range
Good afternoon,
I am trying to return values in a cell based on data in another cell as it falls between a range
Here is the range:
1 Star 299 - 966
2 Star 967 - 1160
3 Star 1161 - 1330
4 Star 1331 - 1509
5 Star 1510 - 2300
I've gotten through 1 Star and 2 Star but am receiving an error when I try to tack on the next range (3).
Here's my formula. All is within the same sheet, no cross sheet references:
=IF([Facility Score]27 >= 299, IF(AND([Facility Score]27 <= 966), "1", IF([Facility Score]27 >= 967, IF(AND([Facility Score]27 <= 1160), "2"))))
The [Facility Score]27 cell contains a number. I want the formula to return a number 1-5 corresponding with the range the number in [Facility Score]27 falls into.
Thanks in advance
Paula
Best Answer
-
Assuming your score never goes above 2300, then start with the top and move your way down:
=IF([Facility Score]27 >1509,5,IF([Facility Score]27 >1330,4,IF([Facility Score]27 >1160,3,IF([Facility Score]27 >966,2,IF([Facility Score]27 >298,1)))))
Answers
-
Assuming your score never goes above 2300, then start with the top and move your way down:
=IF([Facility Score]27 >1509,5,IF([Facility Score]27 >1330,4,IF([Facility Score]27 >1160,3,IF([Facility Score]27 >966,2,IF([Facility Score]27 >298,1)))))
-
Almost perfect! I adjusted the return value a bit. The value in [Facility Score]27 is 1323, and it is returning a value of "3 Star" when it should be "4 Star"
=IF([Facility Score]27 > 1509, "5 Star", IF([Facility Score]27 > 1330, "4 Star", IF([Facility Score]27 > 1160, "3 Star", IF([Facility Score]27 > 966, "2 Star", IF([Facility Score]27 > 298, "1 Star")))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!