Using Negative Values in a Nested If Statement

[JaiWoods]

Hello,

I'm trying to do a nested if statement and my criteria has 2 negative values in it. My formula isn't returning the proper values and i'm not sure why. it seems as though it's reading all negatives values the same and not based on "less than" or "greater than".

So if they reached or exceed goal percentage then put 4, if they are less than goal but within 10% then 3, if less than goal by more than 10% then 2.


Here's a snapshot of my formula and table:

% of Goal Variance % % to Goal
26% -74% 3
71% -29% 3
43% -38% 3
10% -55% 3

96% -4% 3
23% -77% 3
136% 36% 4

 

[alacrity59]

"Essentially, what I'm trying to do is "if the value in F3 is greater than or equal to 0, then put 4, if the value in F3 is less than -10% then put 3, if the value in F3 is less than or equal to -11 then 2, else 1."

I suspect you are not understanding that -11 is less than -10. In the text above you would put 3 for -10.1, -10.5, down to -10.9999999999999 and then -11 and lower you would put 2.

I don't think you would ever have to put a 1.

 

[JaiWoods]

"Essentially, what I'm trying to do is "if the value in F3 is greater than or equal to 0, then put 4, if the value in F3 is less than -10% then put 3, if the value in F3 is less than or equal to -11 then 2, else 1."

I suspect you are not understanding that -11 is less than -10. In the text above you would put 3 for -10.1, -10.5, down to -10.9999999999999 and then -11 and lower you would put 2.

I don't think you would ever have to put a 1.

Thanks for your help.