is it possible to show a decimal like 142.35" as 10'-

[EXCEL$B!!(BNEWS]

hi,

i am using ft and in these days,

is it possible to show a decimal like 142.35" as 11'-10 1/4" in one cell,

cell format seems impossible,any hints

thanks

 

[JLGWhiz]

The only thing I know of is the CONVERT function, which would giver you
11.8625 by coverting inches to feet. I have never tried to format it as 10'
10 1/4". Probably wouldn't take.

 

[Ron Rosenfeld]

hi,

i am using ft and in these days,

is it possible to show a decimal like 142.35" as 11'-10 1/4" in one cell,

cell format seems impossible,any hints

thanks

Excel does not have a "feet/inches" format, but you can convert your value into
a text string.

With your value in A1:

=INT(A1/12)&"'–"&TEXT(ROUND(MOD(A1,12)*4,0)/4,"# ??/??\""")

Note the formula is rounding to the nearest 1/4. If you need to round to 8ths
or 16ths (or anything up to 99ths), change the "4" factor in the above equation
accordingly.
--ron

 

[Ivyleaf]

Excel does not have a "feet/inches" format, but you can convert your value into
a text string.

With your value in A1:

=INT(A1/12)&"'-"&TEXT(ROUND(MOD(A1,12)*4,0)/4,"# ??/??\""")

Note the formula is rounding to the nearest 1/4. If you need to round to 8ths
or 16ths (or anything up to 99ths), change the "4" factor in the above equation
accordingly.
--ron

Ron's answer should give you EXACTLY what you are after. If however,
you would like a slightly more accurate conversion on the fractional
inches part (.35 of an inch would be closer to 1/3 than 1/4 in my
book), then you can shorten Ron's formula down to:

=INT(A1/12)&"'-"&TEXT((MOD(A1,12)),"# #/#\""")

This will round to the nearest fraction with a single digit
denominator: 1/2, 1/3, 1/4, 1/5 etc.

If you want even more precision, change to:

=INT(A1/12)&"'-"&TEXT((MOD(A1,12)),"# #/##\""")

Which will allow a two digit denominator.

Cheers,
Ivan.

 

[Ron Rosenfeld]

Ron's answer should give you EXACTLY what you are after. If however,
you would like a slightly more accurate conversion on the fractional
inches part (.35 of an inch would be closer to 1/3 than 1/4 in my
book), then you can shorten Ron's formula down to:

Not sure where you're from. I live in eastern Maine and that may make a
difference.

Your analysis is correct regarding accuracy, but it has been my experience that
when someone wants measurements in fractional inches displayed as fractions,
they're usually builders or carpenters and so forth, so they generally want the
measurement rounded to some power of 2: e.g. 1/4, 1/8, 1/16 or possibly 1/32.
Machinists, of course, might want more accuracy.

--ron

 

[Peter T]

Ron's answer should give you EXACTLY what you are after. If however,
you would like a slightly more accurate conversion on the fractional
inches part (.35 of an inch would be closer to 1/3 than 1/4 in my
book), then you can shorten Ron's formula down to:

=INT(A1/12)&"'-"&TEXT((MOD(A1,12)),"# #/#\""")

This will round to the nearest fraction with a single digit
denominator: 1/2, 1/3, 1/4, 1/5 etc.

If you want even more precision, change to:

=INT(A1/12)&"'-"&TEXT((MOD(A1,12)),"# #/##\""")

Which will allow a two digit denominator.

Cheers,
Ivan.

Hi Ivan,
What about say 0.9.
First format gives 8/9, ninth's
An interesting jazz time signature or harmonic maybe

Regards,
Peter T

 

[Ivyleaf]

Hi Ivan,
What about say 0.9.
First format gives 8/9,  ninth's
An interesting jazz time signature or harmonic maybe

Regards,
Peter T- Hide quoted text -

- Show quoted text -

Hi,

As I have grown up with decimal all my life, I probably shouldn't have
jumped in to this one .
Ron, I am sure that you are correct in saying "they generally want
the
measurement rounded to some power of 2: e.g. 1/4, 1/8, 1/16 or
possibly 1/32.", which is why I was careful not to disagree with /
discredit your solution. As someone with a 'decimal' brain, I just had
difficulty in calling .35 a quarter .

Cheers,
Ivan.