a warning message

Supposing I understood your question correctly, do the following:

Put in A3
=IF(ABS(A1-A2)>=diff,"Warning","")

and put your value for difference in some cell and this cell "diff" via Name Box.

You might want replace "Warning" with a different message.

Dick: Try this formula which uses nested functions: =IF((A1-A2>1.24),"WARNING",(IF((A1-A2<(-1.24)),"WARNING",A1-A2)))

I hope this is what you were looking for. You didn't specify, but if CELL A1-A2 is within your +/- 1.25 parameter, cell A3 will return the actual value calculated by subtracting A1 from A2.

Note also that to reach your specification you must make the "cut-off value" 1.24 (or some value less that 1.25 such as 1.249, 1.2499,1.2499999, etc.). If you plug 1.25 into the formula and the numbers in cell A1 and A2 were 100 and 98.75, respectively, cell A3 would return the value 1.25 rather than your desired message "Warning."

It is up to you to decide the amount of tolerance you can allow in your specific application. (Are we talking about the measurement of critical blood concentrations of powerful drugs, here, or the measurement of a rough-cut piece of lumber?)

Please post a reply as to how well this works for you. Enjoy!

Jeff Williams

From Jeff: Dick, I tried Aladin's formula and it worked well when I did the following.

I put the value 1.24 in cell C5 and used aladin's formula like this:
=IF(ABS(A1-A2)>=C5,"WARNING","").

Then I thought of an enhancement that returns the value of the calculation A1-A2 if the criteria for a "warning" are not met:
=IF(ABS(A1-A2)>=C5,"WARNING",A1-A2).

I see some advantages and disadvantages for both Aladin's suggestion and my suggestion in my original reply.

Aladin's formula lets you change the criterion in a separate cell, which is handy if your application involves frequently changing parameters, but it makes it harder to copy the formula to multiple cells. You would have to use some absolute cell references which can slow down the copy & paste process.

My fomula has the conveniance of having the critera built right in, so it can be copied to other cell quite easily using relative cell addresses. This is the formula I would use if your parameters are clearly-defined tolerances that don't change, such as in a manufacturing application.

My thanks to Aladin; I learned something this morning! While I was playing with the formula I suggested in my first reply, I tried to get "MS Help" to give me some feedback on "using the absolute value of a number in a calculation." As many of us have found out, "Help" isn't very helpful!

I originally thought I could simply use the mathematical symbol for |absolute value| in a formula, but it doesn't work that way in Excel! "Help" didn't point me toward a named function, but Aladin did. By the time I posted my original reply, Aladin's reply was on the web; it taught me something new and became a springboard for the hybrid formula I suggested above!

Jeff Willams

Dick: I tried something else tht seems to work well. Try this formula in cell A3:
=IF(ABS(A1-A2)>=1.25,"WARNING",A1-A2).

This is what I was really looking for originally, but I didn't know the sytax forthe absolute value function! This formula frees you from having to reference another cell (C5 in my previous example) yet still returns the actual value of the calculation if the "warning" criteria are not met. It is also easily copied using relative cell addresses.

Cheers!

Jeff Williams

Jeff,

When you use a named cell like 'diff', you can copy the formula it's in. It wiil always be an absolute reference. Verify by looking at it thru Insert, Name, Define.

Aladin

Jeff:
Thanks! worked like a charm