Within Tolerance ?

[Decreenisi]

Hi,

Can anyone help.........

In Excel A1:A100 I have a record of measurements taken, i.e A1 12,
A2 3, A3 14, A4 11, A5 15, A6 9, A7 8, A8 6 ect. One number occupies
one cell. In B1:B100 I want to write a formular for the following.

(Cell A3 being the example) Check the value in the cell which is 14
and see if it is within tolerance (10+/- 2).

If the value is <8 it returns a text string saying "Under spec" and
changes the cell colour to red. If the value is >12 it returns a text
string saying "Over spec" and changes the cell colour to Yellow.

I have tried but can't see a way to do it, without over complicating
it.

Thanks in advance

 

[Walter Briscoe]

In message <[email protected]
s.com> of Wed, 24 Jun 2009 02:55:00 in microsoft.public.excel.programmin

Hi,

Can anyone help.........

In Excel A1:A100 I have a record of measurements taken, i.e A1 12,
A2 3, A3 14, A4 11, A5 15, A6 9, A7 8, A8 6 ect. One number occupies
one cell. In B1:B100 I want to write a formular for the following.

(Cell A3 being the example) Check the value in the cell which is 14
and see if it is within tolerance (10+/- 2).

If the value is <8 it returns a text string saying "Under spec" and
changes the cell colour to red. If the value is >12 it returns a text
string saying "Over spec" and changes the cell colour to Yellow.

I have tried but can't see a way to do it, without over complicating
it.

What have you tried and why do you think it is over complicated?
I tried copying through the following formula:
=IF(A1<8,"Under spec",IF(A1>12,"Over spec",""))

That deals with the text.
I added the following conditional formats:
Cell Value is equal to ="Under spec" Format.../Patterns/Color Yellow
Cell Value is equal to ="Over spec" Format.../Patterns/Color Red

Previously, I tried putting a yellow "Under spec" in C1, etc.
The value was copied through, but the colour was not.