In/Out of Tolerence

[Alan Dresch]

Help

Ive got myself in a pickle.

I'm using the IF Function to format a 'remote' cell as a
(go/nogo) green tick.

It's the formula that I am having trouble with.

I have a target value with a plus and minus tolerence and
I want to show the green tick if the sample value is
within the upper and lower limits.

Thanks

Alan Dresch

 

[Andy B]

Alan

Try this:

=IF(AND(cell<higher tolerance,cell>lower tolerance),"P","")
and format it as Wingdings2

Andy

 

[Tushar Mehta]

The only way I can think of doing this without programmatic support is:

The cell that contains the check mark should contain a character that
looks like a check mark -- say, the square root character in the Arial
font. Let's say this cell is C2.

Suppose that the cell whose value is to be checked is B2. The target
value is F1 and the tolerance is G1.

Then, enter the check mark in C2 and format it in green. Now, select
Format | Conditional Formatting... In the resulting dialog box, change
the first drop down to 'Formula Is' In the formula textbox enter =ABS
($B$2-$F$1)>$G$1. Click the Format... button, and in the resulting
dialog box, in the Font tab, set the color to the same color as the cell
background (the default being white).

OK to close all dialog boxes and you are all set.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2004
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office