Return a value if cell falls between two numbers.

Sarah Fuller

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!

Nic Larsen

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

Nic Larsen

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

Sarah Fuller

Hi Nic - thank you! That is what I ended up figuring out just after I posted my question (haha!). Thanks for confirming.

aamiva

=IF([Jan %]@row < 0.9, 0, ([Jan %]@row - 0.9) * 100)

JamesB

@Sarah Fuller

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