IF / AND Statement Not Working

[missdeannamarie]

Hello,

I have seen this answered before, however I have not been able to get the function to work correctly for me. I really appreciate any help that can be given and apologize if this is too similar to something else.

I am looking for a formula that will look between ranges to return a value. Below, I have a chart with a target dollar amount, I also have the actual amount shown. Our tolerance is +/-5%. I have listed what the ranges are for each color (red, yellow and green). The formulas I have listed in I4 and I5 work, however if I add the = to the greater than or less than the formula no longer works when the actual is equal to one of those amounts. For the formula in I3, I cannot get it to work at all, this formula needs to look at the actual and if it is less than G3 but greater than G5 it should be red. When I change the actual to less than that amount it does not return the value I need.

Again, I apologize if this has been answered elsewhere, I just have not been able to find anything that explains why these are not working.

Thank you so much!!!

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]L01[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Tolerance[/TD]
[TD]- Ranges[/TD]
[TD]+ Ranges[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]$916,667[/TD]
[TD]$2,771,331[/TD]
[TD]+/-5%[/TD]
[TD]$779,167[/TD]
[TD]$824,999[/TD]
[TD]Red[/TD]
[TD]=IF(AND(D3>G5,D3<G3),"l","")[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$825,000[/TD]
[TD]$870,832[/TD]
[TD]Yellow[/TD]
[TD]=IF(AND(D3>F4,D3<G4),"l","")[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$870,833[/TD]
[TD]$962,500[/TD]
[TD]Green[/TD]
[TD]=IF(AND(D3>F5,D3<G5),"l","")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[missdeannamarie]

Sorry, Just realized the formulas didn't show completely

RED: =IF(AND(D3>=G5,D3<=G3),"l","")
YELLOW: =IF(AND(D3>=F4,D3<=G4),"l","")
GREEN: =IF(AND(D3>=F5,D3<=G5),"l","")

 

[Special-K99]

G3 is 824999
G5 is 962500

No number can be less than G3 AND greater than G5.
A number less than 824,999 cannot be greater than 962,500

You probably need to use OR rather than AND.

 

[missdeannamarie]

OMG, I can't believe I totally blanked on that, yes it needed to be OR.

Thank you so much!!!