Auto fill in formula

Hello, I am required to fill in a table with some measurements of parts that my firm manufactures. Each quality approved measurement has a tolerance:

• If the measured number has no decimals (x), the tolerance is +/-0.8
• If the measured number has one decimal (x.x), the tolerance is +/-0.46
• If the measured number has two decimals (x.xx), the tolerance is +/-0.18

What I have been trying to do is come up with an auto fill in formula for these tolerances(in tolerance+/tolerance- cells) depending on the quality approved measured number (from nominal number cell), but failed badly. Please help me, I have tons of parts with hundreds of measurements and I will lose my mind.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Attach the file but in the mean time let me know if this helps...............=IF((A1)-TRUNC(A1)=0,"+/-0.8",IF((A1)-TRUNC(A1)<=0.1,"+/-0.46",IF((A1)-TRUNC(A1)>=0.11,"+/-0.18"," ")))

I attached the file, your formula works perfectly, but it needs a little bit of tinkering, which I think I can manage by myself, meaning that I have to delete the +/- that you included as I have separate cells for each value, as you can see if you open the attachment. Thank you very much!

Great.............. All the best!!!

It is working fine for me.........check attached file......(F39), (G39), (H39)

Formula used...........=IF((F32)-TRUNC(F32)=0,"0.8",IF((F32)-TRUNC(F32)<=0.1,"0.46",IF((F32)-TRUNC(F32)<=0.11,"0.18"," ")))

If I give a value with no decimal it is fine. If I give a value which has decimals between x.01 to x.1 it displays 0.46 tolerance which is shouldn't as I only want to display 0.46 tolerance to numbers with only one decimal(x.x). If I give x.11 value it displays 0.18 value and every value above x.11 doesn't display anything.(picture attached)

Originally Posted by nflsales

Try

=if(g32=0,"",if(g32=trunc(g32),"0.8",if(g32=trunc(g32,1),"0.46",if(g32=trunc(g32,2),"0.18"," "))))

, nop it doesn't even recognize it as a formula...

Can you input some numbers in the file and attach it here so that I can work on that. If there are negative values then it wont work.

Try

=if(g32=0,"",if(g32=trunc(g32),"0.8",if(g32=trunc(g32,1),"0.46",if(g32=trunc(g32,2),"0.18"," "))))

FOR SUM USE =SUM(G32,G33) iNSTEAD OF G32+G33 and =
sum(G32,-G34) instead G32-G34

Yeah...........Tudy is correct !!