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 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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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 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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
That worked! Thank you so much!!
-
Wonderful! I'm glad I could help 🙂
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives