Challenging: Conditional format negative-->positive

[sg2004]

In column F14 i have a number that i want to show as RED if it is less than E14 by at least 5%, or GREEN if it is more than E14 by 5%.

I've successfully tried "greater than 1.05 * E14" = Green and "less than 0.95 * E14" is Red.

This doesnt work though as the numbers can sometimes be negative!
It's a bit tricky.

What might help is the absolute function =abs(-6) for example is 6.

But i can't work out how to do this, because sometimes E14 is negative and F14 is a lot greater and therefore positive.

Tricky but hopefully doable? HELP

 

[Peter_SSs]

Would this work for you?

Formula is: =F14>E14+ABS(E14)*0.05 (Green) and similar for red.

 

[sg2004]

Doesn't look like it.
I've selected greater than, and set it to green, and typed in all sorts of numbers a great deal below and above the negative number on the right and it wont turn it green..

 

[sg2004]

if you get it working on a simple sheet (need 2 numbers in the sheet), pleaase let me know. Thanks so much

 

[greencroft]

for the example you cite in row 14:

use conditional formatting with:

Condition 1 as "Formula Is" =F14/E14<=0.95 with stated format RED

Condition 2 as "Formula Is" = F14/E14>=1.05 with stated format GREEN

 

[Peter_SSs]

I've selected greater than, ...

In the Conditional Formatting dialog, have you chosen 'Formula is' from the left hand drop-down?

Are these correct?

Excel Workbook
	E	F	G
14	-100	-94.99
15	-100	-120
16	-100	-102
17	15	-800
18	580	389
19
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F14	1. / Formula is =F14>E14+ABS(E14)*0.05	Abc
F14	2. / Formula is =F14	Abc

 

[sg2004]

no i hadn't.. someone sitting next to me told me formula is, looks at whether the formula is the same... not quite.

OK that looks great, will give it a go, thanks!