Combined IF AND formula with range

Paula Meunier

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

Leibel S

@Paula Meunier

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

Leibel S

@Paula Meunier

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

Paula Meunier

https://community.smartsheet.com/discussion/comment/325407#Comment_325407

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