conditional formatting question

[capri]

Hi,

I'm having a problem with trying to do some conditional formatting.

I have several scores out of 5 such as

3.57 4.00 4.00 2.29 3.43

In column I , there is the average of these which is 3.46


I want to place conditional formatting so that if any of the scores varies from the average by more than 1 the cell will change to red. I tried the formula "Cell Values is equal to I22+1" & "Cell Values is equal to I22-1 ", however the cell containing 2.29 will not change to red.

Can someone tell me what I am doing wrong?

Thanks.

[capri]

Thanks, I'll check those out.

It's Office 97.

[StevePorter]

You had the right idea, however...

Assuming your average is in Cell A1, and your values are in Cells B1:F1, then assign the following Conditional Formatting to each of the value cells:

"Cell Value is" "not between" "=$A$1-1" and "=$A$1+1"

Set the font color to Red.

Contact me if you have any questions...

[capri]

Thanks Steve,

That is what I did.

However I figured out what I was doing wrong. I tried to format the cell red for both the +1 and the -1. Excel didn't like that. When I changed the colour on one to orange it worked.