Excel Conditional Formatting problem

[slipd]

Hi guys,

I'm having a lot of bother presenting some really simple data

Basically, I want to make a simple financial model that highlights whether a particular venture is making any money or not?

I'd like to highlight the actual value red if it falls under 80% of the target value, highlight orange if equal to the target value and highlight green if above the target value. I've tried using conditional formatting but I'm not sure how/if this scenario would work?

Sales.

Target: 500
Actual: 100
Over/Under: -400

Any ideas?

 

[Lartyconshayo]

Nested if statements should do it.

Use a helper cell as well to perform the 80% test on that rather than making the calculation in the IF statements

 

[ArchAnGeL]

You would use formula based conditional formatting and 3 rules

So if the target value is in cell A1 and the actual value is in B1, click conditional formatting on cell B1, new rule, "use formula to determine which cells to format" and then for the formula =$b$1<($a$1*0.8) and then just set the format to red

then another rule where b1=a1 and another where b1>a1

 

[slipd]

You would use formula based conditional formatting and 3 rules

So if the target value is in cell A1 and the actual value is in B1, click conditional formatting on cell B1, new rule, "use formula to determine which cells to format" and then for the formula =$b$1<($a$1*0.8) and then just set the format to red

then another rule where b1=a1 and another where b1>a1

Just the ticket, thanks mate

 

[httk]

Make sure you put them in the right order and test them when done.