Convert and display feet, inches and fractions in excel

[SirSquiddly]

Hi folks

I understand there are several forums on this or similar but I am having a nightmare with it.

I have values displayed in decimal feet which I have converted to metres with no issues, but I now have to display this in inches and fractions as well as feet, inches and fractions. I have managed to get the conversion but cant simplify the fractions E.g., 16/32 is displayed but I would like it to = 1/2 but go to 32nds so there could be 7 5/32

for feet and inches and fractions I have edited a formula I found:

=INT('Decimal Feet'!D10)&"'"&IF(('Decimal Feet'!D10-INT('Decimal Feet'!D10))," - "&INT(('Decimal Feet'!D10-INT('Decimal Feet'!D10))*12)&IF((('Decimal Feet'!D10-INT('Decimal Feet'!D10))*12)-INT(('Decimal Feet'!D10-INT('Decimal Feet'!D10))*12),TEXT((('Decimal Feet'!D10-INT('Decimal Feet'!D10))*12)-INT(('Decimal Feet'!D10-INT('Decimal Feet'!D10))*12)," ?/32"),"")&"""","")

For inches:

='Feet and inches (2)'!


Open to other options and any help much appreciated.

Thanks

 

[Phuynh]

Use MROUND() with TEXT() formating will solve fraction display issue.

Phh

 

[Phuynh]

Here is formula to convert from decimal inches to imperial fraction round-off 1/64:

=IF(NOT(ISNUMBER(A1)),"n/a",
 IF(OR(MROUND(A1,SIGN(A1)*1/64)>=12,MROUND(A1,SIGN(A1)*1/64)<=-12),
 ROUNDDOWN(MROUND(A1,SIGN(A1)*1/64)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(A1),1/64),12),"0 #/##")&"""",
 TEXT(MROUND(A1,SIGN(A1)*1/64),"# #/##")&""""))

Btw, formula above can perform convert negative number!

Also, if you want to use with LAMBDA() function:

=LAMBDA(in,
 IF(NOT(ISNUMBER(in)),"n/a",
 IF(OR(MROUND(in,SIGN(in)*1/64)>=12,MROUND(in,SIGN(in)*1/64)<=-12),
 ROUNDDOWN(MROUND(in,SIGN(in)*1/64)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(in),1/64),12),"0 #/##")&"""",
 TEXT(MROUND(in,SIGN(in)*1/64),"# #/##")&""""))
 )

Cheer!

Phh

 

[Phuynh]

Formula to convert from decimal feet to imperial fraction round-off 1/64:

=IF(NOT(ISNUMBER(A1)),"n/a",
 IF(OR(MROUND(A1*12,SIGN(A1)*1/64)>=12,MROUND(A1*12,SIGN(A1)*1/64)<=-12),
 ROUNDDOWN(MROUND(A1*12,SIGN(A1)*1/64)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(A1*12),1/64),12),"0 #/##")&"""",
 TEXT(MROUND(A1*12,SIGN(A1)*1/64),"# #/##")&""""))

Note: regarding text rounding, the denominator number is importance and must match with number of rounding, ex. 1/2, 1/8 -> #/# ; 1/16, 1/32, 1/64 -> #/## ; 1/4096 -> #/####, etc.

Phh

 

[Phuynh]

Similar to decimal feet conversion on previous post, reuse formula with slight modification
for convert from decimal meter to imperial fraction round-off 1/64:

=IF(NOT(ISNUMBER(A1)),"n/a",
 IF(OR(MROUND(A1/.0254,SIGN(A1)*1/64)>=12,MROUND(A1/.0254,SIGN(A1)*1/64)<=-12),
 ROUNDDOWN(MROUND(A1/.0254,SIGN(A1)*1/64)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(A1/.0254),1/64),12),"0 #/##")&"""",
 TEXT(MROUND(A1/.0254,SIGN(A1)*1/64),"# #/##")&""""))

Phh