Formula to colour in cells depending upon the relationship of 2 other cells

[ajt8888]

I am trying to automate a process where I have to colour in cells depending upon the relationship of a result figure to a target and depending upon the aim.

The three colours are:
Green - where the result is better than the target
Amber - Where the result although worse than the target is within 10%
Red - Where the result worse than the target and outside the 10% (More than 10%)

The target may be greater than or less than depending upon the aim.

I have been given a formula, which is in the attached sheet and which works for some of the 12 different scenarios but not others. I'd be very grateful for any help.

[martindwilson]

you need to be consistent in using % or numbers as 100%=1

[ajt8888]

you need to be consistent in using % or numbers as 100%=1

I have no choice some of the measures are % some are numbers, I dont mind having a different formula if its a % or a number or 4 different formulas depending upon the numeral, % and aim. But you will see that the formula I currently have is giving a range of answers

[martindwilson]

the 3 rules would be
=B2<C2 green
=AND(B2>C2,B2<=C2+0.1*C2) amber
=AND(B2>C2,B2>C2+C2*0.1) red
(it doesn't matter if they are whole numbers or percentages after all )

[martindwilson]

tidied up a bit

=B2<C2 green
=AND(B2>C2,B2<=C2*1.1) amber
=B2>C2*1.1 red

[ajt8888]

Thank you but I have tried these and they dont do anything. It just says its an error

[ajt8888]

I just want a formula that says if Cell X is less than Cell Y then its Green or if Cell X is up to 10% more than Cell Y its Amber or if Cell X 11% or more than Cell Y = Red.

Then I need to be able to reverse that formula to say Cell X is more than Cell Y then its Green or if Cell X is up to 10% less than Cell Y its Amber or if Cell X 11% or less than Cell Y = Red.

[ajt8888]

I just want a formula that says if Cell X is less than Cell Y then its Green or if Cell X is up to 10% more than Cell Y its Amber or if Cell X 11% or more than Cell Y = Red.

Then I need to be able to reverse that formula to say Cell X is more than Cell Y then its Green or if Cell X is up to 10% less than Cell Y its Amber or if Cell X 11% or less than Cell Y = Red.

[martindwilson]

error where does it say error? if you want to colour the cells then use cf
if you just want words then see column d

[Aajt8888]

Thanks very much that's great, very helpful.