Old school IF question with a twist?

[Lowell In the south]

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

 

[Yogi Anand]

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

 

[Juan Pablo González]

This

=MOD(A1,1/32)

will return 0 if the number in A1 is a multiple of 1/32, i.e:

Book2
	A	B	C	D
1	1/64	0.015625
2	1/32	0
3	3/64	0.015625
4	1/16	0
5	5/64	0.015625
6	3/32	0
7	7/64	0.015625
8	1/8	0
9	9/64	0.015625
10	5/32	0
11	11/64	0.015625
12	3/16	0
13	13/64	0.015625
14	7/32	0
15	15/64	0.015625
16	1/4	0
Sheet1

 

[Juan Pablo González]

BTW, 2/32 doesn't have a 5 in the fifth place... it's 0.06250

 

[Lowell In the south]

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]

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
	B	C	D	E
1	original		roundedtonearest1/16
2	1/32		1/16
3	2/32		1/16
4	3/32		2/16
5	4/32		2/16
6	5/32		3/16
7	6/32		3/16
8	7/32		4/16
9	8/32		4/16
10	9/32		5/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]

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]

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]

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