Formula that includes tolerances (engineering)

E1 = B1-A1 - IF(B1>A1, C1, D1)

Like that?

F1 =IF(A1+C1<B1, "Above tolerance!", IF(A1+D1>B1, "Below Tolerance!", "A-OK!"))

Yeah that works. Thankyou.

However when the value hits the tolerance upper or lower limit (example :24.95 or 25.05 it displays #######) ??????

sorted it. thankyou so much

Hey again. It would appear that i need my figures to show zero if they hit the nominal figure of 25. (24.95 to 25.05) 25 should = 0 but obviously shows as -0.05????

Huh, how about that.

Those pound signs are a minor error: they're the "value wider than cell" code, so if you just make the cell wider, it will display it. But it should be right around 0...?

I'm getting 7.07767E-16

So we found a floating-point rounding error.

Wrap it with ROUND:

E1=ROUND(B1-A1 - IF(B1>A1, C1, D1), 0.001)

That will round to the nearest thousandth. I picked thousandths because it's a 10th of your tolerance precision so you should have plently of space.

Originally Posted by btc84

Hey again. It would appear that i need my figures to show zero if they hit the nominal figure of 25. (24.95 to 25.05) 25 should = 0 but obviously shows as -0.05????

???

Obviously if it's smack on nominal you'll deliver the whole tolerance. That's what the math was supposed to do, right?

What exactly are you trying to deliver, here?

When you add that last syntax in it seems to affect the deviation. Example if input 24.94 its shows zero in cell as opposed to -0.01.

Hmm... splitting it like this:
E1 = B1 - A1
E2 = IF(B1>A1, C1, D1)
E3 = E1 - E2

That solved everything.

So we've got some kind of issue with the logical function not really returning a number properly.

Do this instead:

E1 = (B1-A1) - VALUE(IF(B1>A1, C1, D1))

Wrapping the IF with the VALUE will force it to assess back to a number before doing the arthmatic. That solved all the problematic behaviors we've seen.

Basically as an example:

upper tol = 25.05 (With your last syntax if you input 25.06 it shows deviation as 0.00)
nominal figure = 25 (This is now corrected when the nominal is inputted)
lower tol = 24.95 (24.94 and deviation shows 0.00 ????)

obvioulsy before we added round and 0.001 the above problems were ok.

Hey again. Thanks for the help so far. Still having a problem with the nominal figure deviation. Example: If Nominal is 25 and Actual is 25 it should read 0.00. Instead it reads -0.05.??? I know this is a pain.

Alright, I don't understand what you want to accomplish, like, mathamatically.

You have a nominal value, a real value, and upper + lower tolerance limits.

What do you want your output to be?

The difference between nominal and real values? How do you want to relate this to the tolerances?

For me, I would do something like, simply "Nominal - Real" to get the difference, and then check if that's outside tolerance and throw a "Go/NoGo" in an adjacent cell...?