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.

Hi, try
=MROUND(CONVERT(F11,"m","ft"),1/32)

"Tereena" wrote:

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.

There are several possible approaches
1) http://www.cpearson.com/excel/FeetInches.htm
2) http://archive.baarns.com/excel/faq/xd_dtyp1.asp#6
3) Type a value such as 4.5 in a cell and give the cell the format # ??/12
to display 4 6/12
4) With 4.5 in A1, in B1 use =INT(A1)&" ft "&MOD(A1,1)*10&" in" to see 4 ft
5 in. You will not be able to use B1 in any calculation
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

Make that: =INT(A4)&" ft "&TEXT(MOD(A4,1)*12,"#")&" in"
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

On Thu, 4 Dec 2008 08:50:35 -0800, Tereena
wrote:

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