conditional Formatting Help

[Repoort]

I hope I am able to explain what I am looking to do....

I would like to use conditional Formatting in a table where there is a value in cell E18 and a value in D18. I would like the value in E18 to turn red if the value in D18 is 3% or more than the value in E18. If the value in D18 is not 3% or more than in E18 I would like E18 to turn green. If E18 is empty then I would like it to stay the shaded gray I have it. To figure the percent difference between the two cells I have to use ((D18-E18)/D18) which gives me the % difference I just cant seem to get the color change of the cell based off the percentage. If I have not explained this good I apologize, I think I have been looking at this too long....

 

[C with no eyes]

Conditional formatting applied to E18 =E18<103/100*D18 Format fill to green

Conditional formatting applied to E18 (another rule) =E18<=103/100*D18 Format fill to red and font to white

 

[FDibbins]

To add to what C suggested, and to take care of the empty cells...
=and(E18<>"",E18<1.030*D18) Format fill to green

 

[Repoort]

I can't get these formulas to work how I want them to. If I have 20,000 in D18 and 19,417 in E18, E18 should be red
((20,000-19,417)/19,417)=.03002 or 3.002%
If i have 20,000 in D18 and 19,428 in E18, E18 should be green
((20,000-19,428)/19,428)=.02997 or 2.997%
I am just using these numbers as examples but this is what I am trying to accomplish. This formula seems to make it red if e18 is 3% more than d18 and I can't seem to make it ever turn green.

 

[Repoort]

Thinkni figured it out. I am sure I was confusing in my explanation of what I was trying to do. In the top two formulas you gave me c with no eyes, I changed e18 with d18 and changed the colors for the rules. Have tested it and so far seems to work. Thank you for the help and I hope it keeps working