Return a value if cell falls between two numbers.
Hi all --
I'm trying to write a formula that will return a point value (0-10) depending on the percentage of a column. For example: if the percentage column is 100%, then I'd want "10" to be entered in the "Points" column; if the percentage column is between 99% or above but below 100%, I'd want "9" to be entered in the "Points" column, and so on and so forth. If the percentage column is less than or equal to 90%, the points column would say "0".
Here's the formula I've written so far, but it's just returning "0":
=IF([Jan %]9 = 100, 10, (IF(AND([Jan %]9 <= 99, [Jan %]9 > 98), 9, (IF(AND([Jan %]9 <= 98, [Jan %]9 > 97), 8, (IF(AND([Jan %]9 <= 97, [Jan %]9 > 96), 7, (IF(AND([Jan %]9 <= 96, [Jan %]9 > 95), 6, (IF(AND([Jan %]9 <= 95, [Jan %]9 > 94), 5, (IF(AND([Jan %]9 <= 94, [Jan %]9 > 93), 4, (IF(AND([Jan %]9 <= 93, [Jan %]9 > 92), 3, (IF(AND([Jan %]9 <= 92, [Jan %]9 > 91), 2, (IF(AND([Jan %]9 <= 91, [Jan %]9 > 90), 1, (IF([Jan %]9 < 90, 0)))))))))))))))))))))
(I know, it's long!)
Here's a screenshot of this section of the sheet:
Thanks for any help you can provide!
Best Answer
-
Try it like this:
=IF([Jan %]@row = 1, 10, IF(AND([Jan %]@row <= .99, [Jan %]@row > .98), 9, IF(AND([Jan %]@row <= .98, [Jan %]@row > .97), 8, IF(AND([Jan %]@row <= .97, [Jan %]@row > .96), 7, IF(AND([Jan %]@row <= .96, [Jan %]@row > .95), 6, IF(AND([Jan %]@row <= .95, [Jan %]@row > .94), 5,IF(AND([Jan %]@row <= .94, [Jan %]@row > .93), 4, IF(AND([Jan %]@row <= .93, [Jan %]@row > .92), 3, IF(AND([Jan %]@row <= .92, [Jan %]@row > .91), 2, IF(AND([Jan %]@row <= .91, [Jan %]@row > .90), 1, IF([Jan %]@row < .90, 0
Answers
-
Try it like this:
=IF([Jan %]@row = 1, 10, IF(AND([Jan %]@row <= .99, [Jan %]@row > .98), 9, IF(AND([Jan %]@row <= .98, [Jan %]@row > .97), 8, IF(AND([Jan %]@row <= .97, [Jan %]@row > .96), 7, IF(AND([Jan %]@row <= .96, [Jan %]@row > .95), 6, IF(AND([Jan %]@row <= .95, [Jan %]@row > .94), 5,IF(AND([Jan %]@row <= .94, [Jan %]@row > .93), 4, IF(AND([Jan %]@row <= .93, [Jan %]@row > .92), 3, IF(AND([Jan %]@row <= .92, [Jan %]@row > .91), 2, IF(AND([Jan %]@row <= .91, [Jan %]@row > .90), 1, IF([Jan %]@row < .90, 0
-
Hi Nic - thank you! That is what I ended up figuring out just after I posted my question (haha!). Thanks for confirming.
-
=IF([Jan %]@row < 0.9, 0, ([Jan %]@row - 0.9) * 100)
-
Smartsheet will see the Percentages as decimals even when displayed as a Percent.
Try this..
=If([Jan %]=1,10,If([Jan %]>=0.99,9,if([Jan %]>=.98,8,0))) etc...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!