Conditional Format - Negative Values

[CB241]

Hello,

I am having difficulty with a conditional format equation which involves negative values.

I have entered 4 rules which work for actuals vs budget. I now have my RAG status sorted, but only for positive numbers.

The rules are shown below

Missed target by more than 5% - RED
Missed target by less than 5 % - AMBER
Achieved target by less than 5% - LIGHT GREEN
Achieved target by more than 5% - GREEN

Now that i have entered in actuals. Any value that is negative is showing the wrong colour. I have the following problem
Actual (42,000) vs Budget (37,000) It is showing the RAG status as Green (as £42k is more than £37k)

I want to be able to show this negative value in RED and not Green. How do i do this? I will need the formulas for each colour.


The formulas i have used so far are:-

=1-$AI6/$AJ6>0.05 RED
=AND(1-$AI6/$AJ6<=0.05,1-$AI6/$AJ6>0) AMBER
=AND(($AI6/$AJ6)-1<0.05,($AI6/$AJ6)-1>0) LIGHT GREEN
=($AI6/$AJ6)-1>0.05 GREEN

Please can someone help me to apply additional rules to the above that take into account negative numbers being more or less etc than the budget figures.

Many thanks

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[CB241]

I think you have posted this under the wrong question

[TMS]

I think you have posted this under the wrong question

Why would you think that?

We don't want/need to make up workbook/worksheet/data/formulae that you already have.

Help us to help you.