Conditional Formatting - Unable to get excel to use my values.

[jacob@thepenpoint]

Hello All


I have a worksheet in Excel 2010 where I am trying to use conditional formatting to highlight a cell according to 1 of 3 possible values.

The low value is <=(((3800*16)/6)*.95)
The median value is between (((3800*16)/6)*.95) and (((3800*16)/6)*1.05)
The high value is >=(((3800*16)/6*1.05)

We have an acceptable range of +- 5% of the ideal and we are wanting to highlight the quantity produced so the supervisor can follow up and recount production or explain the variance.

I have been able to get the high and low but they split at the point of >< ((3800*16)/6) and leave no middle ground so it is always either too low or too high according to the highlighting.

Any help would be greatly appreciated.

[TMS]

Rule 1: =AND(A1<>"",A1<=(((3800*16)/6)*0.95))
Rule 2: =AND(A1<>"",A1>(((3800*16)/6)*0.95), A1< (((3800*16)/6)*1.05))
Rule 3: =AND(A1<>"",A1>=((3800*16)/6*1.05))

Assuming you are formatting cells starting with A1; adjust as required.

Regards

[jacob@thepenpoint]

Rule 1: =AND(A1<>"",A1<=(((3800*16)/6)*0.95))
Rule 2: =AND(A1<>"",A1>(((3800*16)/6)*0.95), A1< (((3800*16)/6)*1.05))
Rule 3: =AND(A1<>"",A1>=((3800*16)/6*1.05))

Thanks! That worked like a charm! I think I had confused Excel and it didn't know what to do.

Thanks!