Converting numbers to text based on range of values

jhickok

Hello,

I am trying to create a formula to convert the following values to text based on the ranges below:

I created a separate sheet to reference the scoring ranges and incorporated that into the formula below.

Here is the formula I attempted to use but am getting an "Invalid Operation" message"

=IF([What Numeric]@row < ={Failed}, "Failed to Achieve", IF([What Numeric]@row < ={Partially}, "Partially Achieved", IF([What Numeric]@row < ={Fully}, "Fully Achieved", IF([What Numeric]@row < ={Exceeded}, "Exceeded", [What Numeric]@row < ={Distinguished}, "Distinguished"))))

If working properly, the numeric value in the "What Numeric" column should automatically populate text into the "What" Rating column:

Genevieve P.

Hi @jhickok

Since you've built your table out so nicely, I would actually write this formula a little differently. Instead of using IF statements, I'd use one INDEX(COLLECT to evaluate your 3 columns as one whole range each.

=INDEX(COLLECT({Rating Column}, {Min Column}, <=[What Numeric]@row, {Max Column}, >=[What Numeric]@row), 1)

This way you can add/update your table and the formula will auto-adjust without you needing to add any more IF statements. See: Formula combinations for cross sheet references

Let me know if that makes sense and works for you!

Cheers,

Genevieve