# For every multiple of - formula needed

#### Tristin

##### New Member
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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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?

Last edited:
Incredible. thank you so much!

 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>

Replies
3
Views
561
Replies
13
Views
791
Replies
0
Views
118
Replies
5
Views
2K
Replies
1
Views
802

Threads
1,203,543
Messages
6,056,023
Members
444,840
Latest member
RazzelDazel

### 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

### 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