Is it possible to display feet and inches in one cell ?

T

Tereena

Microsoft Excell 2007 - I am trying to convert a number to feet and inches.
As far as I can tell it will only show feet or inches but not both such as
1ft 5in.
 
R

Ron Rosenfeld

Microsoft Excell 2007 - I am trying to convert a number to feet and inches.
As far as I can tell it will only show feet or inches but not both such as
1ft 5in.

Is this only for display purposes, or do you want to use the value in
subsequent formulas.

If only for display purposes, then, with rounding to the nearest 1/16th inch,
and with decimal inches in A1, try:

=IF(ABS(A1)=MROUND(ABS(A1),1/16),"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
MROUND(MOD(ABS(A1),12),1/16),"#-#/##\"""))&IF(A1<0,")","")

1. This requires the Analysis tool Pak to be installed, or the use of Excel
2007. If it is not installed (see Excel HELP for how to do that), then the
MROUND function calls should be replaced with:

ROUND(num*16,0)/16

so:

=IF(ABS(A1)=ROUND(ABS(A1)*16,0)/16,"","~")&IF(A1<0,"(","")&IF(
INT(ABS(A1/12))>=1,INT(ABS(A1/12))&"' ","")&TRIM(TEXT(
ROUND(MOD(ABS(A1),12)*16,0)/16,"#-#/##\"""))&IF(A1<0,")","")

IF you want to round to some other factor, change the "16's" to whatever factor
you wish. (DON't change the 12's).

If you need to use the value in subsequent calculations, this approach will not
work. You will have to retain the original value someplace, and use the above
formula only for display.
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top