Tolerances in Excel

Robm1955

I want to work out the difference from a measurement, and its tolerance. So if I have a measurement of 24.235, and the tolerance is ± 0.25, and the figure is out of tolerance, I want to show the figure, but if the measurement is in tolerance, I want it to show 0.00. I want the out of tolerance figures not to show + or -, just the result. So -0.05 would show as 0.05.

megaginge

I don't understand.

What are your inputs?

Measurement
Tolerance
Figure?

You'd have to input the figure right? Let's call this the Normal measurement

If so I'd have 4 columns, 3 in one out

Normal measure
Actual Measure
Allowed Tolerance
Calculated Measure

Calculated would apply the logic you are mentioning ... But then you also mention showing the tolerance measurement? which confuses me ...

So you could have this setup (Top row A, second row B, 1 thru 4 respectively)

Normal measure,	Actual measure,	Tolerance,	Calculated measure
27.575,	27.6,	0.25,	0.00

The only formula is in the Calculated measure field which does this:

=IF((A2-B2<C2),"0.00",B2)

But then I don't understand what you mean by tolerance. Do you want to show the difference from tolerance when tolerance is allowed? Should that be in the calculated measure field where I placed 0.00? Or somewhere else again?
If you wanted it in the next field, do this...

Difference from tolerance
0.025

The 0.025 field, which I have in E2, has this formula:

=ABS(IF(D2="0.00",A2-B2,"N/A"))


I have no idea if this helps as I don't understand the question, but enjoy.

Dave

Le_Kirk

Use IF statements. Example, IF the measurement is within tolerance, display 0.00 or out of tolerance display the figure. Then format the cell with the out of tolerance figure to be number only.

Robm1955

Sorry for being a bit vague. We have to measure some components, and want to record the results. The target is 24 ± 0.25, so if they are in tolerance, I want to show 0.00, if they are out, I want to show by how much. eg 24.3 shows 0.05 out of tolerance.

megaginge

Robm1955 wrote: »

Sorry for being a bit vague. We have to measure some components, and want to record the results. The target is 24 ± 0.25, so if they are in tolerance, I want to show 0.00, if they are out, I want to show by how much. eg 24.3 shows 0.05 out of tolerance.

Adapt the usage of ABS and IF that I have provided in my examples, then.

d0nkeyk0ng

Just had a play in excel and I would go with something like:

=IF(AND(D5>23.75,D5<24.25),B5,D5-B5

OR

=IF(AND(D11>C11,D11<E11),B11,D11-B11)

B5 = target measurement
C5 = min tolerance
D5 = actual measurement
E5 = max tolerance
F5 = value (which is target measurement if in tolerance and difference if out of tolerance)

Ainsley1

I don't know the reasoning for your chosen presentation of the OOT analysts you are doing but the method outlined above would possibly meet your need.

However I would add that there is conditional formatting that could be used to highlight (colour, bold etc.) the results which helps to visualise the analysed data....
....but it does sound like you are doing some quality analysis and there are many proven methods for doing that such as run charts and six sigma methods amongst others that you perhaps should have a look at if you are doing this for business purposes and https://en.m.wikipedia.org/wiki/Seven_Basic_Tools_of_Quality might give you a starter. Recognised statistical and graphical methods ease presentation, understanding and completeness.

System

Hi

After reading the above I did this...

5 Columns
ACTUAL, DIFF, EXCESS, UNDER BY, OVER BY:
(I interlaced the formulas as well).
###############################################
Actual.......Diff............Formula
22.00.........2.000.........=SUM(A5-24)
25.00.........1.000.........=SUM(A6-24)
24.00.........0.000..........=SUM(A7-24)

then
EXCESS....Formula
YES...........=IF(((A5-24)<-0.25)OR((A5-24)>0.25),"YES"," ")
YES...........=IF(((A6-24)<-0.25)OR((A6-24)>0.25),"YES"," ")
...................=IF(((A7-24)<-0.25)OR((A7-24)>0.25),"YES"," ")

Followed by

UNDER BY.......FORMULA
1.750..................=IF((B6<-0.25),(B6 + 0.25)," " )
.......................... =IF((B6<-0.25),(B6 + 0.25)," " )
...........................=IF((B7<-0.25),(B7 + 0.25)," " )


OVER BY.........FORMULA
..........................=IF((B5>0.25),(B5 - 0.25)," " )
0.75.................. =IF((B6>0.25),(B6 - 0.25)," " )
..........................=IF((B7>0.25),(B7 - 0.25)," " )



Somewhat crude but does fit the criteria?

andybirch

This is my go at the answer:


if A1 contains the value you want to assess and B1 shows the Standard and C1 shows the result, the formula in C1 would be:


=IF(A1>(B1*1.25),A1-(B1*1.25),IF(A1<(B1*0.75),B1-A1,0))


Hope it helps.




Andy.