Forum Discussion
Formulas and Functions
Hi, I have a Tolerance range of 10cm, I would like to be able to incorporate a formula in my spreadsheet that says "If my measured value is within the -5 to +5cm tolerance range of a total of 10cm, then return no value (ie a "0"), but, if the measured value exceeds the range of -5 to +5cm, then return only the difference between the measured value and the tolerance of 10cm". For example, I measure -12cm, the returned value is then -2cm.
Greatly appreciate the help!.
Does this attached file solve it?
8 Replies
- Hi Huxley
Could you provide a few more examples
Base Measure Result
10 -12 2
10 6 ?
10 -6 ?
10 5 ?
10 -5 ?Hi Wyn, thanks for replying. Appreciate your help.
Tolerance is 10cm (made up of +5cm through -5cm range)
My actual measurement is a result number compared to the "control/ tolerance allowed" of the range
For example, my actual measurement is +4cm (ie within the range) - using the "IF" argument, I ask that because the measured result is within the tolerance range, return a result of 0 (because no action is needed). When the measured result is a positive, I have no problem, but, when the returned measured result is a negative number, I find it difficult to compute a formula that takes this into account and compares it to the tolerance range.
O13 N13 RESULT
10.00 8.00 0 =IF(O13>N13,O13-N13,0)
10.00 14.00 4 4 points in excess of the tolerance
10.00 -6.00 0 Should return a result of (-1) because it is negative one point in excess of the tolerance.
The table I am working on below..
Ref Tolerance Result 1 position position 2 position position 3 10.00 12.00 2 4 10.00 8.00 0 5 10.00 -8.00 0 Problem 6 10.00 -12.00 0 Problem 7 10.00 18.00 8 8 10.00 22.00 12 9 10.00 25.00 15 Please ignore reference points 1&2.
It would be great if I could have a cell with the negative tolerance and a second cell with the positive tolerance. In that way I can specify a -3 and plus 7 for example. It seems to me that I will have to specify a plus and minus tolerance to excel in order for it to understand the range that it needs to work with?.
Thanks for your help, much appreciated.
- Hi Huxley,
I'm not quite clear on this sorry Huxley.
So the 10 is the equivalent of +/- 5
Then is 12 the equivalent of +/- 6 therefore 1 over and 1 under = 2
8 is +/-4 so within the +/-5 so you show zero?
In theory I think you can change your formula to
=IF( ABS(O13)>N13, ABS(O13) -N13,0)
ABS gives you the absolute value and therefore addresses the negative issue. Does that help?
