show fractions rounded up to nearest 1/16th inch

[John G.]

I have cells formatted as fraction, up two digits. We are a mfg. company
cutting metal and if it is not on a tape measure, we don't use it. If we
input in an odd decimal value, we can get stuff like 17/20 or other wierd
unusable fractions. Is there a way to have the same cell of input to show a
result rounded up to the absolute nearest 1/16th of an inch?

 

[akphidelt]

Wouldn't the formatting to sixteenths work?

If you go to Format-->Cells-->Number-->Fraction

Then click on As Sixteenths?

Or does that not meet your criteria?

 

[Sandy Mann]

If you don't have "As 16th (8/16)" as an option in the Fraction formatting
list then try a Custom format of # ??/16

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[John G.]

I looked at that..., but if it amounts to 1/4", we want it to show 1/4", not
4/16", that will slow down the process doing the conversion in the shop. I
know, picky, picky.

John

 

[David Biddulph]

Not in the same cell, but =CEILING(A7,1/16) and format as # ??/16

 

[David Biddulph]

Well, if you do want to be picky, try
=IF(MOD(CEILING(A1,1/16),1)=0.5,TEXT(CEILING(A1,1/16),"#
?/2"),IF(MOD(CEILING(A1,1/16),0.5)=0.25,TEXT(CEILING(A1,1/16),"#
?/4"),IF(MOD(CEILING(A1,1/16),0.25)=0.125,TEXT(CEILING(A1,1/16),"#
?/8"),TEXT(CEILING(A1,1/16),"# ?/16"))))

 

[John G.]

David,

Can I nest your code with

=IF(F10+$H$9=$H$9,0,F10+$H$9) This references the cells that we want to
have rounded up values in. This simply adds a number such as 3/4" or 7/8" or
another arbitrary number to f10 for a finish size.

 

[Ron Rosenfeld]

I have cells formatted as fraction, up two digits. We are a mfg. company
cutting metal and if it is not on a tape measure, we don't use it. If we
input in an odd decimal value, we can get stuff like 17/20 or other wierd
unusable fractions. Is there a way to have the same cell of input to show a
result rounded up to the absolute nearest 1/16th of an inch?

I'm not sure what you mean by "rounded up to the absolute nearest 1/16th of an
inch". Rounding UP does not necessarily go to the *nearest* 1/16th. For
example, 1/16 + 1/64 would ROUND to 1/16 but would ROUNDUP to 2/16 (or 1/8).

Either can be done in the cell of input, using a macro, but you need to decide
which you want, and also whether it is OK to actually change the value that you
have inputted to be rounded, or rounded up, to the appropriate 16th.

Finally, you won't be able to ROUNDUP in the cell of input and NOT change the
inputted value.
--ron

 

[John G.]

David,
This works great, but the results in the cell show a outlined square between
my round number and my fraction. Not displaying a space properly. Wierd. But
thanks for the code. Now to do something about the space being displayed by a
square. I tried to paste it here but it did not show.

 

[Ron Rosenfeld]

Well, if you do want to be picky, try
=IF(MOD(CEILING(A1,1/16),1)=0.5,TEXT(CEILING(A1,1/16),"#
?/2"),IF(MOD(CEILING(A1,1/16),0.5)=0.25,TEXT(CEILING(A1,1/16),"#
?/4"),IF(MOD(CEILING(A1,1/16),0.25)=0.125,TEXT(CEILING(A1,1/16),"#
?/8"),TEXT(CEILING(A1,1/16),"# ?/16"))))

Might be simpler to just use:

=CEILING(A2,1/16)

and then custom format the cell as # ??/??
--ron

 

[John G.]

Ron,

That works and without a oddly displayed spacing character. This site has
been a gem to use. Thanks again.

John

 

[John G.]

Ron,

How do I meld your answer with this =IF(F10+$H$9=$H$9,0,F10+$H$9) in the
same cell?

 

[John G.]

Ron,

I have taken everything that you have suggested and came up with a solution
that works just fine. Thank goodness we aren't going to the moon on my work
here. We make storefront or window framing here, and we have a plus minus of
1/8". So rounding up to another 16th of inch will not really hurt us. What it
does is keeps us covered on the plus side for material amounts and costs.
Better to be a tad up that low. We can always take some away, but adding on
when not budgeted can be problematic. Thanks for all your help. I should have
posted in the newbie area.

John

 

[David Biddulph]

Presumably =IF(F10=0,0,CEILING(F10+$H$9,1/16)) ?

 

[Ron Rosenfeld]

Ron,

I have taken everything that you have suggested and came up with a solution
that works just fine. Thank goodness we aren't going to the moon on my work
here. We make storefront or window framing here, and we have a plus minus of
1/8". So rounding up to another 16th of inch will not really hurt us. What it
does is keeps us covered on the plus side for material amounts and costs.
Better to be a tad up that low. We can always take some away, but adding on
when not budgeted can be problematic. Thanks for all your help. I should have
posted in the newbie area.

John

Glad your solution is working.

--ron

 

[aiky]

Hi John,

I think my dad is in the same industry as you and he told me to do something like yours. I'm stuck and google for solution when i come across this website. I convert everything into mm to do some calculation but I don't know how to convert it back to the fraction in inch. Do you mind sharing how you go about doing it? Thanks.

 

[Gord Dibben]

Assume you have 10 mm in a A1

In B1 enter =A!/25.4

Format to Number>Fractions>Sixteenths

Display will be 6/16


Gord Dibben MS Excel MVP