Conditional Formating

Evening,

Any help would be appreciated; I know the very basics about conditional formatting through the wizard, but haven't got a clue how to do this one. I've attached the sheet to help explain.

(I'm using Excel:Mac 2011, but can access the MS version if needed)

I would like to colour format columns D and E against column C. So for D if the value is above C it would turn green, equal to C turn orange, and below C it would turn red. However, I want it to ignore the decimal point, so if target was 3.4, then a value in the English column of 3.5 or 3.3 would turn orange, and only a value of 2.99 or lower would turn red, and only a value of 4.0 would turn green. Unfortunately I need to keep the decimal points in the column, so I can't just change it into whole numbers.

Any help would be awesome.

Thanks

Maybe use INT(...) in your comparison.


Regards, TMS

1. highlight the range you want to apply the conditional formatting to (D2:D16)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =INT($C2)>INT($D2) Format Fill the color you want

Repeat 3 and 4 for the other 2 rules

=and($e2<>"",int($c2)>int($e2))

You're welcome. Thanks for the rep.

Happy to help, thanks for the feedback