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:

[TABLE="width: 739"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Desired measurement[/TD]
[TD]actual measured[/TD]
[TD]Tolerance (mm)[/TD]
[TD]diff[/TD]
[TD]Issue?[/TD]
[TD]Deviation after tolerance[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]100[/TD]
[TD]100.2[/TD]
[TD]0.25[/TD]
[TD]0.2[/TD]
[TD]Within tolerance[/TD]
[TD]blank[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]125[/TD]
[TD]100.5[/TD]
[TD]0.25[/TD]
[TD]24.5[/TD]
[TD]Issue[/TD]
[TD]24.25[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]130[/TD]
[TD][/TD]
[TD]0.35[/TD]
[TD]130[/TD]
[TD]Issue[/TD]
[TD]129.65[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]151[/TD]
[TD][/TD]
[TD]0.45[/TD]
[TD]151[/TD]
[TD]Issue[/TD]
[TD]150.55[/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="width: 1103"]
<colgroup><col><col><col span="10"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]0.35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]0.45[/TD]
[TD][/TD]
[TD]mytable[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]0.55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0.65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]0.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]0.85[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]0.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]1.15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]1.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]1.35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]1.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]1.55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]col B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]1.65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]475[/TD]
[TD="align: right"]1.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1.85[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]measurement[/TD]
[TD]nominal[/TD]
[TD]tolerance[/TD]
[TD]min[/TD]
[TD]max[/TD]
[TD]decision[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]row 22[/TD]
[TD="align: right"]199.5[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0.65[/TD]
[TD="align: right"]199.35[/TD]
[TD="align: right"]200.65[/TD]
[TD]PASS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]124.5[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]124.65[/TD]
[TD="align: right"]125.35[/TD]
[TD]FAIL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]formula giving first pass[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]=IF(AND(B22>=E22,B22<=B22),"PASS","FAIL")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]formula giving the first tolerance (0.65)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]=VLOOKUP(C22,mytable,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]