Excel 2007 Conditional Formatting help

[robertma34]

Hi all,

Would be very grateful for some conditional formatting help, I have a spreadsheet that shows estimated number of days, actual days and the difference

A (Estimate) B (Actual) C (% Diff)
4 9 125%
20 18 -10%
5 3 -40%
6 10 67%
2 5 150%
4 4 0%

I'd like to amend the background colour for column C if the following conditions are met, (i) the estimated days (Column A) are >=5 AND (ii) the percentage (Column C) is >= 20% OR >=-20%. In this example -40% and 67% would be highlighted.

Grateful for any help.

Thanks

Matt

[TMS]

Assuming data starts in row 2, select cell C2 down to the end of the data (column).

Condition: =IF(AND(A2>=5, OR(C2<=-0.2, C2>=0.2)))


Regards, TMS

[Fotis1991]

In CF rules

=OR(AND($A1>=5,$C1>=20%),AND($A1>=5,$C1<=-20%))

[Ace_XL]

Maybe..

=AND(A2>=5,ABS(C2)>=0.2)

[robertma34]

Thank you so much for the great help and speedy replies.

I've added the following (=OR(AND($A1>=5,$C1>=20),AND($A1>=5,$C1<=-20)) and am now nearly there. Anything >= 5 and a difference of 20% or -20% will be highlighted in RED, I've also added a CF to highlight in GREEN anything with an estimate <5. My only issue now is the numbers than fall between these two parameters (i.e >-5 but less than 20% or -20%.

I've attached my effort so far, I'd be grateful for any further advise.

[Fotis1991]

Perhaps..

=AND($B3>ABS(5),$D3<ABS(20))

[robertma34]

Many thnaks to everyone for your help

[Fotis1991]

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[TMS]

You're welcome. Thanks for the rep.