Return a value if cell falls between two numbers.
Hi all 
I'm trying to write a formula that will return a point value (010) 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!