Can Excel display fractions for distances (measured in inches) with the
lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google. You
would think with the construction industry being so large that this would be
a piece of cake. I've played around with Custom Number Format but haven't
found a solution. Any ideas?

I typed all your numbers 5 8/16, 3 10/16, and 1 13/16 in blank cells and
excel converted them to the decimals you show and displayed them the way you
say you want.

xl2003

--
Regards,
Tom Ogilvy

"Dave B" wrote in message
...
Can Excel display fractions for distances (measured in inches) with the
lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google. You
would think with the construction industry being so large that this would
be
a piece of cake. I've played around with Custom Number Format but haven't
found a solution. Any ideas?

if you choose
Numberformat: "Fraction/Up to two digits" = # ??/??

you'll get what you want...

PROVIDED the VALUE in the cell is a multiple of 1/16

(control rounding with ceiling/floor
(or mround from analysis toolpak)

=ceiling(4.321,1/16) = 4 3/8


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dave B" wrote:

Can Excel display fractions for distances (measured in inches) with
the lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google.
You would think with the construction industry being so large that
this would be a piece of cake. I've played around with Custom Number
Format but haven't found a solution. Any ideas?

The numbers are calculated from other cells and so are 5.5, 3.625, 1.8125.
I'm trying to get them to display in 16ths of an inch (rounded down to 8ths,
4ths, or halfs when applicable). Sorry I didn't clarify. It's amazing
Microsoft hasn't fixed this. A whole industry having to rely on third-party
add-ins or really complex formulas.



"Tom Ogilvy" wrote in message
...
I typed all your numbers 5 8/16, 3 10/16, and 1 13/16 in blank cells and
excel converted them to the decimals you show and displayed them the way
you
say you want.

xl2003

--
Regards,
Tom Ogilvy

"Dave B" wrote in message
...
Can Excel display fractions for distances (measured in inches) with the
lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google. You
would think with the construction industry being so large that this
would
be
a piece of cake. I've played around with Custom Number Format but
haven't
found a solution. Any ideas?

Thanks. Can you elaborate more on how to round the value to the nearest
16th?


"keepITcool" wrote in message
...
if you choose
Numberformat: "Fraction/Up to two digits" = # ??/??

you'll get what you want...

PROVIDED the VALUE in the cell is a multiple of 1/16

(control rounding with ceiling/floor
(or mround from analysis toolpak)

=ceiling(4.321,1/16) = 4 3/8


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dave B" wrote:

Can Excel display fractions for distances (measured in inches) with
the lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google.
You would think with the construction industry being so large that
this would be a piece of cake. I've played around with Custom Number
Format but haven't found a solution. Any ideas?

Just to add:

The formatting Excel placed on the cells was under format=Cells= number
tab, under fractions, up to two digits (21/25) and if queried in VBA is
showed it to be:

# ??/??

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
I typed all your numbers 5 8/16, 3 10/16, and 1 13/16 in blank cells and
excel converted them to the decimals you show and displayed them the way
you
say you want.

xl2003

--
Regards,
Tom Ogilvy

"Dave B" wrote in message
...
Can Excel display fractions for distances (measured in inches) with the
lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google. You
would think with the construction industry being so large that this
would
be
a piece of cake. I've played around with Custom Number Format but
haven't
found a solution. Any ideas?

format as # ??/16

but you can't have both I don't believe - rounding and lowest denominator.

--
Regards,
Tom Ogilvy

"Dave B" wrote in message
...
Thanks. Can you elaborate more on how to round the value to the nearest
16th?


"keepITcool" wrote in message
...
if you choose
Numberformat: "Fraction/Up to two digits" = # ??/??

you'll get what you want...

PROVIDED the VALUE in the cell is a multiple of 1/16

(control rounding with ceiling/floor
(or mround from analysis toolpak)

=ceiling(4.321,1/16) = 4 3/8


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dave B" wrote:

Can Excel display fractions for distances (measured in inches) with
the lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google.
You would think with the construction industry being so large that
this would be a piece of cake. I've played around with Custom Number
Format but haven't found a solution. Any ideas?

How about this, where the number of inches is in decimal format in cell B2:

=ROUND(16*B2,0)/16

then using Fraction -- "Up to two digits" number format?

Seems to be working ok for me for now. But strange that Microsoft hasn't
simplified this by creating a number format targeted towards the
construction industry.


"Tom Ogilvy" wrote in message
...
format as # ??/16

but you can't have both I don't believe - rounding and lowest denominator.

--
Regards,
Tom Ogilvy

"Dave B" wrote in message
...
Thanks. Can you elaborate more on how to round the value to the nearest
16th?


"keepITcool" wrote in message
...
if you choose
Numberformat: "Fraction/Up to two digits" = # ??/??

you'll get what you want...

PROVIDED the VALUE in the cell is a multiple of 1/16

(control rounding with ceiling/floor
(or mround from analysis toolpak)

=ceiling(4.321,1/16) = 4 3/8


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dave B" wrote:

Can Excel display fractions for distances (measured in inches) with
the lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google.
You would think with the construction industry being so large that
this would be a piece of cake. I've played around with Custom
Number
Format but haven't found a solution. Any ideas?

On Thu, 28 Oct 2004 13:33:34 -0400, "Dave B" wrote:

Can Excel display fractions for distances (measured in inches) with the
lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google. You
would think with the construction industry being so large that this would be
a piece of cake. I've played around with Custom Number Format but haven't
found a solution. Any ideas?


Apply this formula to your results if you want display to the closest 1/16th
rounded to the LCD:

=ROUND(A1*16,0)/16

Format the cell as fraction with up to two digits
# ??/??


--ron

Hi Dave :
I'm in the Construction industry (20 years)

The only drawing I seen expressed in 16nth was a truss drawing
your not in the truss business are you?

Construction 16ths come on!

I must be missing something here if not, try entering the number as follows.

whole number space numerator/denominator

Whole number spacebar numerator / denominator
5 10 / 16
results
5 5/8 and still has the integerity of a number

Good Luck
TK


"Ron Rosenfeld" wrote:

On Thu, 28 Oct 2004 13:33:34 -0400, "Dave B" wrote:

Can Excel display fractions for distances (measured in inches) with the
lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google. You
would think with the construction industry being so large that this would be
a piece of cake. I've played around with Custom Number Format but haven't
found a solution. Any ideas?


Apply this formula to your results if you want display to the closest 1/16th
rounded to the LCD:

=ROUND(A1*16,0)/16

Format the cell as fraction with up to two digits
# ??/??


--ron

Dave

I guess i did miss something; but, even with different formated numbers
entered in a1 b1 c1 I still received the result
in whole number and fraction notation.??.

A1 B1 C1 =A1+B1+C1
5.5 3.625 1.8125 10 15/16 format as fraction(3)
5.5 3.625 1.8125 10.9375 no format

good Luck
TK


"TK" wrote:


Hi Dave :
I'm in the Construction industry (20 years)

The only drawing I seen expressed in 16nth was a truss drawing
your not in the truss business are you?

Construction 16ths come on!

I must be missing something here if not, try entering the number as follows.

whole number space numerator/denominator

Whole number spacebar numerator / denominator
5 10 / 16
results
5 5/8 and still has the integerity of a number

Good Luck
TK


"Ron Rosenfeld" wrote:

On Thu, 28 Oct 2004 13:33:34 -0400, "Dave B" wrote:

Can Excel display fractions for distances (measured in inches) with the
lowest common denominator?

For example...
5 8/16 (5.5) displayed as 5 1/2
3 10/16 (3.625) displayed as 3 5/8
1 13/16 (1.8125) displayed as 1 13/16

I'm amazed I haven't been able to find an answer to this on Google. You
would think with the construction industry being so large that this would be
a piece of cake. I've played around with Custom Number Format but haven't
found a solution. Any ideas?


Apply this formula to your results if you want display to the closest 1/16th
rounded to the LCD:

=ROUND(A1*16,0)/16

Format the cell as fraction with up to two digits
# ??/??


--ron