Decimal to Feet / Inch Converstion

[savv37]

Hello, I'm trying to convert decimals into feet and inches. I have this formula, =INT(D5) & " ft " & TEXT(MOD(D5,1)*12, "0, #/##") & " in", however, the results are often in fractions that are not common like the 1/32", 1/64", 1/8", 1/4" & 1/2" fractions. Is there a way I can modify this formula so that the fractions are presented in more common construction fractional units?

Regrettably, I'm unable to use the XL2BB. It seems that Excel is preventing the macro from functioning, as the functions are grayed out and cannot be used.

Thanks for taking the time to review & I appreciate all your assistance. Thanks in advance.

 

[Micron]

I think you will have to use a VLookup table as suggested here

Convert Decimal to dimensional fraction VBA?

My initial thought on this is to use a vlookup table with the decimal values, separate columns for numerator/denominator, and build the result as a string: It’s always going to display “slightly small” … but depending on how deep you want to go with the table you’ll be close

community.spiceworks.com

 

[Phuynh]

Don't use "INT" function if you plan to convert negative number to feet-inches, use "ROUNDDOWN" instead.
"ROUNDDOWN" with "MROUND" round the number to 1/64 (or 1/2, 1/4, 1/16, 1/32 etc.), so "TEXT" can
properly formating imperial fraction part (the denominator).

Your original formula has problem with convert negative numbers when using INT(), try this:

=ROUNDDOWN(D5,0) & " ft " & TEXT(MROUND(MOD(ABS(D5),1)*12,1/64), "0 #/##") & " in"

Phh

 

[Phuynh]

Hello, I'm trying to convert decimals into feet and inches. I have this formula, =INT(D5) & " ft " & TEXT(MOD(D5,1)*12, "0, #/##") & " in", however, the results are often in fractions that are not common like the 1/32", 1/64", 1/8", 1/4" & 1/2" fractions. Is there a way I can modify this formula so that the fractions are presented in more common construction fractional units?

Regrettably, I'm unable to use the XL2BB. It seems that Excel is preventing the macro from functioning, as the functions are grayed out and cannot be used.

Thanks for taking the time to review & I appreciate all your assistance. Thanks in advance.

Following from my previous post, I think this may be useful to you.

Excel formula for convert feet-inches to decimal value, and
Excel formula for convert decimal value to feet-inches, vice versa.

Just plain Excel formulas (no VBA) for quick convert from decimal to Imperial feet-inches, and vice versa
when I do B.O.M./ schedule for my drawings, or share workbook to ppl don't use VBA (like my boss and my co-worker, Mac users)

Strongly recommend but not require to define "Excel Name" to hold conversion factors such as:

in=1 (default inch if not specify)
ft=1/12
yd=1/36
mi=1/63360 (mile)
mm=25.4
cm=2.54
m=0.0254
...

LAMBDA function for convert Imperial feet-inches (in various formats)
to decimal value, with optional argument conversion to-factor [cFact].

UDF/ Excel Name: todec()

=LAMBDA(imperial,[cFact],
LET(si,IF(LEFT(imperial,1)="-",-1,1),
    ft,IFERROR(ABS(VALUE(LEFT(imperial,(FIND("'",imperial)-1)))),0),
    in,TRIM(SUBSTITUTE(SUBSTITUTE(IFERROR(RIGHT(imperial,LEN(imperial)-FIND("'",imperial)),imperial),"-",""),"""","")),
    si*(ft*12+VALUE(IF(ISERR(AND(FIND(" ",in),FIND("/",in))),IFERROR(VALUE("0 "&in),in),in)))/IF(ISOMITTED(cFact),1,1/cFact))
)

LAMBDA function for convert decimal value to imperial architectural format,
with optional argument conversion from-factor [cFact] with fixed 1/64 round-off.

UDF/ Excel Name: toimpa()

=LAMBDA(decimal,[cFact],
LET(si,IF(LEFT(decimal,1)="-",-1,1),
    rd,1/64,
    cf,IF(ISOMITTED(cFact),1,cFact),
    IF(NOT(ISNUMBER(decimal)),"n/a",
    IF(OR(MROUND(decimal/cf,si*rd)>=12,MROUND(decimal/cf,si*rd)<=-12),
    ROUNDDOWN(MROUND(decimal/cf,si*rd)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(decimal/cf),rd),12),"0 #/####")&"""",
    TEXT(MROUND(decimal/cf,si*rd),"# #/####")&"""")))
)

In addition, here are some test/experiment functions, these will work with imperial array:
Note that these functions use UDF/ Excel Name todec() and toimpa() functions from above.

UDF/Excel Name: sumtoimpa() - Similar to Excel SUM() function

=LAMBDA(imperials,toimpa(SUM(MAP(imperials,todec))))

UDF/Excel Name: sumtodec() - with optional argument convert to factor [cFact] - Similar to Excel SUM() function

=LAMBDA(imperials,[cFact],(SUM(MAP(imperials,todec))*IF(ISOMITTED(cFact),1,cFact)))

UDF/Excel Name: averageimpa() - Similar to Excel AVERAGE() function

=LAMBDA(imperials,toimpa(AVERAGE(MAP(imperials,todec))))

UDF/Excel Name: minimpa() - Similar to Excel MIN() function

=LAMBDA(imperials,toimpa(MIN(MAP(imperials,todec))))

UDF/Excel Name: maximpa() - Similar to Excel MAX() function

=LAMBDA(imperials,toimpa(MAX(MAP(imperials,todec))))

UDF/Excel Name: rangeimpa() - Similar to Excel (MAX() - MIN()) functions

=LAMBDA(imperials,toimpa(MAX(MAP(imperials,todec))-MIN(MAP(imperials,todec))))

UDF/Excel Name: largeimpa() - Similar to Excel LARGE() function

=LAMBDA(imperials,rank,toimpa(LARGE(MAP(imperials,todec),rank)))

UDF/Excel Name: smallimpa() - Similar to Excel SMALL() function

=LAMBDA(imperials,rank,toimpa(SMALL(MAP(imperials,todec),rank)))

Happy Holidays!

Phh

Some test,