Forum Discussion
Formulas and Functions
Does this attached file solve it?
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?Hi Wyn. Thanks for taking the time and interest to help.
I'm afraid the solution suggested will not work. If the Tolerance is exceeded by the actual measurement, I only need to see by how much.
If I may, think of it this way;
I have a flat surface, where the measurement point on that flat surface is 0cm. I then place an object on top of that flat surface, I measure the height, this is 3cm. Similarly, I place an object underneath this flat surface, I measure the negative height, -3cm. In both instances, the formula I seek should return a "0" value because each measured height is within the 10cm tolerance range, ie +5cm through -5cm. I have a problem with formulating the negative part of the formula, vis instead of subtracting, I should add, but how do I do this in the same formula because each single measured value could be either positive or negative - ie, it's completely unknown until I measure it. The formula needs to be 'intelligent' enough to recognise if the measured value is positive or negative and then apply the correct subtraction or addition?.
Now, on the same flat surface, I place another object, measure the height = +6cm. Thus my positive height is +1cm because it is one value higher or, exceeding, the +5cm tolerance. I only need to see by how much the tolerance is exceeded. Hence the "if" argument.
Here is my problem, on the same surface, I place an object underneath the flat surface, I measure the height on the minus scale = -8cm, thus my sought after return value should be -3cm because it exceeds the -5cm tolerance by 3 units.
Again, help appreciated, it really seemed so simple to work out!.
- Thanks for the detailed explanation Huxley. So in your earlier example where you provided a table the measurement of 12 resulted in a 2 (but it sounds like it should be +7). And the measurement of 8 resulted in 0 but it sounds like it should be +3 ?