For every multiple of - formula needed

[Tristin]

So I'm trying to write a program to automatically fill in specifications of parts at work for quality inspection. our general tolerances for parts are +/-.25mm up to 125mm, then .1mm for every 25mm thereafter. If the measurements are within specifications, nothing needs to be displayed. If they aren't, I need to show how much deviation there is AFTER our acceptable tolerances (99% of the time +/- 0.25) I have the function for +/-0.25 in working order. The formula I have written out for after 125mm is:

y=((A1-125)z+0.25)
Y= deviation tolerance.
Z= for every multiple of 25=0.1mm.

If possible, id like it in one function as opposed to 2 conflicting functions for y<125 and y>125. I'll show you what I got so far.

=SUM(B13-D13)
*desired measurement - actual measurement*
H13 is the product of the function above

=IF(H13>=0,(H13-0.25),(H13+0.25))
*determines whether deviation is positive or negative to add or subtract from actual measurement*

=IF(AND(H13>=(-0.25),H13<=0.25),"IN TOLERENCE",I13)
*determines whether to paste deviation AFTER tolerance has been added/subtracted*
I13 is the amount of deviation after the tolerance has been added to the actual measurement

Thanks to anyone for even attempting to help. it'd really free up about 3 hours at work each day, so I can get more done.

Tristin

 

[W8253]

Hi Tristin,

It is a little hard to visualise your file, so I took the liberty of structuring it myself, hope this helps:

	B	C	D	E	F	G
12	Desired measurement	actual measured	Tolerance (mm)	diff	Issue?	Deviation after tolerance
13	100	100.2	0.25	0.2	Within tolerance	blank
14	125	100.5	0.25	24.5	Issue	24.25
15	130		0.35	130	Issue	129.65
16	151		0.45	151	Issue	150.55

<tbody>
</tbody>

Col B and C are your data.

Col D: =IF(B13<=125,0.25,IF(B13<=150,0.35,IF(B13<=175,0.45,IF(B13<=200,0.55,"not specd")))) --> you can keep following the same logic and replace not specd with extra IFs, it currently goes up to 200mm as your largest part.

Col E: =ABS(B13-C13)

Col F: =IF(E13>D13,"Issue","Within tolerance")

Col G: =IF(F13="Issue",E13-D13,"blank")


I purposely left C15 and C16 blank to show what the formula does.

Hope this helps?

 

[Tristin]

Incredible. thank you so much!

 

[oldbrewer]

							1	0.25
							125	0.35
							150	0.45		mytable
							175	0.55
							200	0.65
							225	0.75
							250	0.85
							275	0.95
							300	1.05
							325	1.15
							350	1.25
							375	1.35
							400	1.45
							425	1.55
	col B						450	1.65
							475	1.75
							500	1.85
	measurement	nominal	tolerance	min	max	decision
row 22	199.5	200	0.65	199.35	200.65	PASS
	124.5	125	0.35	124.65	125.35	FAIL
	formula giving first pass
	=IF(AND(B22>=E22,B22<=B22),"PASS","FAIL")
	formula giving the first tolerance (0.65)
	=VLOOKUP(C22,mytable,2)

<colgroup><col><col><col span="10"></colgroup><tbody>
</tbody>