Old school IF question with a twist?

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55
I need to be able to check the result of a formula for 32nds of an inch. If my formula returns a 32nd any 32nd 1/32, 3/32,...29/32 ect. I will some how (Macro or Function) sub tract a 1/16 from one of the user inputs to in effect round the result. Due to my application I am unable to simply round the result as it would not be the true result.

I had thougt of using a IF function that checked the cell for .****5 as a 32nd will always have a 5 in the fifth place after the decimal. But the functions dont like * for any charcter approch. SO I am looking of help!!!!!!!!!!!!!

THANKS!! FOR LOOKING!!!!!!!!!!!!!!!!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
A little more clarification please! If you have a reading that is in 1/32nd's of an inch, would you want to subtract 1/16 of an inch from it, or would you like to round it up or down to the nearest 1/16th.

If you can provide what would you like the following figures to be rounded to ...<PRE>
original rounded
1/32
2/32 1/16
3/32
4/32 2/16
5/32
6/32 3/16
...
...</PRE>

_________________
Regards!
Yogi Anand
This message was edited by Yogi Anand on 2002-11-10 01:05
 

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55

ADVERTISEMENT

Sorry for the lack of information!!!

THis is work sheet produces cut lengths for a saw. This length (RESULT) is output from a longer length (INPUT) that it must then fit within thightly. Some times the result is in even, 32nd, 16th, 8th, 4ths. OUr saw only has stops to cut in 16ths or larger increments. However if I simply round the RESULT down with out srinking the INPUT length we will not have a tight fit of RESULT length within the INPUT lenght. However, I only desire to reduce the INPUT length if the formula RESULT length is expressed in a 32nd.

Yes I am aware that NO even number/32 will have a 5 in the fifth decimal due to the fact that that fraction can be expressed as a 16th, 8th or 4th. THANKS AND sorry I am trying to be clear with out wasting you time.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
If I understand you correctly, you want to round your dimensions to the nearest 1/16th of an inch -- if it is so, then you can use the MROUND function.
Please look at the following simulation
y021109h1.xls
BCDE
1originalroundedtonearest1/16
21/321/16
32/321/16
43/322/16
54/322/16
65/323/16
76/323/16
87/324/16
98/324/16
109/325/16
Sheet8
</SPAN>

Please post back if this is what you are looking for -- otherwise explain a little further and let us take it from there.
 

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55

ADVERTISEMENT

I am making a cut sheet for window grids(Muntins). Like a tic tack toe board (#). With a frame around the perimerter. The lenght of the Muntins with in the frame are dependant on the size of the frame. THe horizontal Muntins are one piece from one side of the frame to the other. The vertical Muntins have to be cut to fit on top between the horizontal Muntin and the top of the frame and cut to fit on bottom between the horizontal Muntin and the bottom of the frame. This is were the 32nds come from.

I have a formula that figures the cut length of the vertical muntins based on the height of the frame less the width and number of horizontal muntins. Some times it results in a 32nds our saw will only has stops for 16th and greater. I can't simply round the vertical Muntin length down there will be a visible gap between the frame and the horizontal Muntin.

What I would like to do if the formula results in a 32nd is to subtract 1/16th from the frame height. Possibly using an if statment. THANKS FOR Your HELP!!!!!!!!!!!!!!!
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
To break your problem down in a way that a formula can be created in Excel, can you tell when you come across 1/32nd's, whether you

1. always ROUNDUP to 1/16th's
2. follow some other criterion -- if you follow some other criterion -- what is that criterion.

In your descriptions, you are providing a good description of the trade practices, but please describe clearly in terms of numerics.

You also stated, you want to have a formula using the IF function. Perhaps you have created a formula and it is not working. Can you post:

1. what is the formula you have
2. what are the arguments in the formula
3. what is the result you are getting
4. what is the result you expect
5. if possible post this information, using Colo's utility (downable from this site -- for information scroll down to the very bottom of MrExcel page.

I believe, once the necessary information is clearly delineated, the necessary formula will not be hard to create. Your clear and specific response to each of the questions asked in this post will greatly help in developing the formula you are looking for.
 

attc

Board Regular
Joined
Oct 11, 2002
Messages
87
one approach: use Juan Pablo's formula adjusted to check for 1/16ths.

=IF(AND(MOD(RESULT,1/32)=0,MOD(RESULT,1/16)<>0),INPUT-1/16,INPUT)

obviously replace RESULT and INPUT with the appropriate cell references or use insert->name->define.

ps. if you want to automatically adjust the original input though, you will need to adjust your original formula. someone may be able to help with this if you let us know what your original formula (f(INPUT) = RESULT) is.
This message was edited by attc on 2002-11-10 11:39
 

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

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