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


Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!