Formula-Convert decimal to foot and inch

[bamaisgreat]

I cant seem to figure out the formula for the following. I need to enter the Decimal and it give the fraction in feet and inch in the next cell.

Decimal Fraction
767.7188 63'11 23/32"

 

[Tetra201]

Try this:

=INT(A1/12)&"' "&TRIM(TEXT(MOD(A1,12),"# ??/32"))&CHAR(34)

 

[AkaTrouble]

above code beat me to it post edited

 

[bamaisgreat]

Works Great! Do you have an add on if A1 is blank show nothing instead of 0'0" in B1

 

[dUBBINS]

=if(a1="","",int(a1/12)&"' "&trim(text(mod(a1,12),"# ??/32"))&char(34) )

 

[AkaTrouble]

works great! Do you have an add on if a1 is blank show nothing instead of 0'0" in b1

=if(a5<=0,"",int(a5/12)&"' "&trim(text(mod(a5,12),"# ??/32"))&char(34))

 

[Tetra201]

I do:

=IFERROR(INT(1/(1/A1)/12)&"' "&TRIM(TEXT(MOD(A1,12),"# ??/32"))&CHAR(34),"")

 

[amanjitkhalsa]

thanks

 

[HomeDeliveryDirect]

Hello,
The formula is what I am looking for but would like the final figure to be rounded to the nearest inch so eg 6.653543307' with last formula would return 6' 7 5/6" but I would like it rounded to the nearest inch so, in this case, looking for 6' 8". If a returning figure was 6' 7 1/6" I would like in round to 6' 7".
Hope someone can help.
Thanks.
Kevin

 

[Rick Rothstein]

The formula is what I am looking for but would like the final figure to be rounded to the nearest inch so eg 6.653543307' with last formula would return 6' 7 5/6" but I would like it rounded to the nearest inch so, in this case, looking for 6' 8". If a returning figure was 6' 7 1/6" I would like in round to 6' 7".

I think this formula will do it...

=INT(A1)+(12*MOD(A1,1)>=11.5)&"'"&IF(12*MOD(A1,1)>=11.5,0,ROUND(12*MOD(A1,1),0))&""""

Note: I am assuming that foot-mark (the apostrophe) on your decimal foot value is not really in the cell, rather, I assume it is there from a cell format.