Conditional format based on percentage of a value

[Saarek]

My spreadsheet has two financial columns. Column A has a financial target in £'s, column B has the amount in £'s that was actually brought into the business.

I want to conditionally format column B to show the colour red if less than 80% of the target value is achieved, amber if 80-99% is achieved and green if 100% or more is achieved.

I know that this can be done with a formula based conditional format with three rules, however the formulas that I have tried have all resulted in the entire column being highlighted in just one colour.

The spreadsheet is an excel 2010 spreadsheet.

Thank you for your help.

 

[Saarek]

Works fine for me.

By the way the formula for green should be =$H2/$E2>=1

Hello again, all fixed. Changed the red formula to =$H2/$E2<0.79, perhaps a conflict with Amber.

 

[WaterGypsy]

Thanks Norie, it is far closer now! Cells with a value of £0 under actual are not going red despite there being a target amount in place.

Do you know how to fix that?

Sorry ... been away - good to see Norie is helping though I was a bit brief earlier I meant to remove the $ in front of the numberic part of the cell reference..

Anyway ..... the problem with £0 could be because you have a divide by zero error which isn't triggering the formatting so I would make the red formula

=or(e2=0, h2/e2<0.8)


Not sure why some aren't working .... is the range that the conditional formats cover complete? Try putting the same data into a row that is already working.

 

[Saarek]

Sorry ... been away - good to see Norie is helping though I was a bit brief earlier I meant to remove the $ in front of the numberic part of the cell reference..

Anyway ..... the problem with £0 could be because you have a divide by zero error which isn't triggering the formatting so I would make the red formula

=or(e2=0, h2/e2<0.8)


Not sure why some aren't working .... is the range that the conditional formats cover complete? Try putting the same data into a row that is already working.

Thanks WaterGypsy, I'll give your method a try as it may prove more accurate than my workaround. All working now.