feet and inches

[Dave B]

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?

 

[Tom Ogilvy]

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

 

[keepITcool]

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]

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.

 

[Dave B]

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

 

[Tom Ogilvy]

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:

# ??/??

 

[Tom Ogilvy]

format as # ??/16

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

 

[Dave B]

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.

 

[Ron Rosenfeld]

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

 

[TK]

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

 

[TK]

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