Converting numbers to text based on range of values
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:
Best Answer

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 autoadjust 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
Answers

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

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

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

That worked! Thank you so much!!

Wonderful! I'm glad I could help 🙂