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?

Yes, there is a way to have the same cell of input to show a result rounded up to the absolute nearest 1/16th of an inch. Here are the steps:

1. Select the cell or cells that you want to format.
2. Right-click and select "Format Cells".
3. In the Format Cells dialog box, select "Fraction" under the "Category" list.
4. Under the "Type" list, select "Up to two digits (1/4)".
5. Click "OK" to close the dialog box and apply the formatting.

Now, any input you enter into the cell will be displayed as a fraction with up to two digits, rounded to the nearest 1/4 inch. However, since you need to round to the nearest 1/16th inch, we need to take an additional step.

1. Select the cell or cells that you want to format.
2. Right-click and select "Conditional Formatting".
3. In the Conditional Formatting dialog box, select "New Rule".
4. Under "Select a Rule Type", select "Format only cells that contain".
5. Under "Format only cells with", select "Cell Value" and then "greater than or equal to".
6. In the value box, enter the formula "=ROUND(A1*16,0)/16" (assuming the cell you are formatting is A1).
7. Click "Format" and select the formatting you want to apply to the cell when the condition is met (e.g. a different font color or background color).
8. Click "OK" to close the dialog boxes and apply the formatting.

Now, any input you enter into the cell will be displayed as a fraction with up to two digits, rounded to the nearest 1/16th inch. If the input is not already a multiple of 1/16th inch, the cell will be formatted with the additional formatting you selected in step 7.

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?

"John G." wrote:

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?

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


Replace @mailinator.com with @tiscali.co.uk


"John G." wrote in message
...
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 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

"akphidelt" wrote:

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?

"John G." wrote:

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?

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

"John G." wrote in message
...
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?

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"))))
--
David Biddulph

"John G." wrote in message
...
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

"akphidelt" wrote:

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?

"John G." wrote:

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?

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.

"David Biddulph" wrote:

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"))))
--
David Biddulph

"John G." wrote in message
...
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

"akphidelt" wrote:

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?

"John G." wrote:

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?

On Thu, 8 May 2008 09:25:03 -0700, John G.
wrote:

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

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.

"David Biddulph" wrote:

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"))))
--
David Biddulph

"John G." wrote in message
...
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

"akphidelt" wrote:

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?

"John G." wrote:

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?

On Thu, 8 May 2008 18:58:29 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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"))))
--
David Biddulph

Might be simpler to just use:

=CEILING(A2,1/16)

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

Ron,

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

John

"Ron Rosenfeld" wrote:

On Thu, 8 May 2008 18:58:29 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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"))))
--
David Biddulph

Might be simpler to just use:

=CEILING(A2,1/16)

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

Ron,

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

"Ron Rosenfeld" wrote:

On Thu, 8 May 2008 18:58:29 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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"))))
--
David Biddulph

Might be simpler to just use:

=CEILING(A2,1/16)

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

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

"Ron Rosenfeld" wrote:

On Thu, 8 May 2008 09:25:03 -0700, John G.
wrote:

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

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

"John G." wrote in message
...
Ron,

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

"Ron Rosenfeld" wrote:

On Thu, 8 May 2008 18:58:29 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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"))))
--
David Biddulph

Might be simpler to just use:

=CEILING(A2,1/16)

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

On Thu, 8 May 2008 13:10:03 -0700, John G.
wrote:

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

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.

Assume you have 10 mm in a A1

In B1 enter =A!/25.4

Format to NumberFractionsSixteenths

Display will be 6/16


Gord Dibben MS Excel MVP


On Fri, 16 May 2008 22:42:28 -0700, aiky wrote:

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.