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

Michael Culley

Looks like you forgot the last IF in your formula (the one for distinguished).

jhickok

Thanks, Michael! I fixed that but am still getting the "Invalid Operation" message.

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

jhickok

That worked! Thank you so much!!

Genevieve P.

Wonderful! I'm glad I could help 🙂