For every multiple of - formula needed

Tristin

New Member
Joined
Mar 2, 2016
Messages
2
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

W8253

Board Regular
Joined
Oct 7, 2015
Messages
64
Hi Tristin,

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

BCDEFG
12Desired measurementactual measuredTolerance (mm)diffIssue?Deviation after tolerance
13100100.20.250.2Within toleranceblank
14125100.50.2524.5Issue24.25
151300.35130Issue129.65
161510.45151Issue150.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?
 
Last edited:

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
10.25
1250.35
1500.45mytable
1750.55
2000.65
2250.75
2500.85
2750.95
3001.05
3251.15
3501.25
3751.35
4001.45
4251.55
col B4501.65
4751.75
5001.85
measurementnominaltoleranceminmaxdecision
row 22199.52000.65199.35200.65PASS
124.51250.35124.65125.35FAIL
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top