convert decimal inches into feet and inches

[Jason Stripling]

I am using the following formula to convert decimal inches into feet and
inches. The problem I have is that the formula is not reducing the fractions.
For instance, when the figure is 35.625 it converts it to 2'-11 10/16"
instead of 2'-11 5/8". I need to keep the inches in 16th's but want the
formula to reduce the fraction when needed. Any suggestions on how to make
this happen?

=INT(F5/12)&"'-"&TEXT(MOD(F5,12),"# ??/16")&""""

 

[Dave Peterson]

I'd start at David McRitchie's site
http://mvps.org/dmcritchie/excel/fractex1.htm

From work done by Bernie Deitrick.

 

[Jason Stripling]

I actual dug a little deeper yesterday and found out how to do it. The
formula is shown below.
=INT(F5/12)&"'-"&TEXT(MOD(F5,12),"# ??/??")&""""
The only issue I have with this one is that it has extra spaces in the final
numbers when you don't have four numbers for the fraction. For instance,
2'-11 5/8" is actually 2'-11 5/8 ". It replaces the missing ?, from the
formula, with a space. This is close enough for me. If anyone knows how to
fix it though, let me know.

 

[Yachtsman]

I actual dug a little deeper yesterday and found out how to do it. The
formula is shown below.
=INT(F5/12)&"'-"&TEXT(MOD(F5,12),"# ??/??")&""""
The only issue I have with this one is that it has extra spaces in the final
numbers when you don't have four numbers for the fraction. For instance,
2'-11 5/8" is actually 2'-11 5/8 ". It replaces the missing ?, from the
formula, with a space. This is close enough for me. If anyone knows how to
fix it though, let me know.

"Dave Peterson" wrote:

> I'd start at David McRitchie's site
> http://mvps.org/dmcritchie/excel/fractex1.htm
>
> From work done by Bernie Deitrick.
>
> Jason Stripling wrote:
> >
> > I am using the following formula to convert decimal inches into feet and
> > inches. The problem I have is that the formula is not reducing the fractions.
> > For instance, when the figure is 35.625 it converts it to 2'-11 10/16"
> > instead of 2'-11 5/8". I need to keep the inches in 16th's but want the
> > formula to reduce the fraction when needed. Any suggestions on how to make
> > this happen?
> >
> > =INT(F5/12)&"'-"&TEXT(MOD(F5,12),"# ??/16")&""""
>
> --
>
> Dave Peterson
>

Enter 23.999 and you'll see that the formula's provide here have bugs. This will resolve them:

=MROUND(A1,1/16)/12 & "' " & TEXT(MOD(MROUND(A1,1/16),12),"#-#/##") & """"

Notice the use of the pound sign (#) rather than the question mark (?); this prevents that extra spacing that you wanted removed; the pound will not insert blanks, the question mark will.

The MROUND function requires that the Analysis Tool Pack is installed; it's easy to research how if you don't already have it installed.

This will display zeros for inches and or feet. E.g. 0' 0"" or 0' 1-3/4" or 2' 1/16"

the 1/16 rounds to the nearest 1/16th inch; it's easily changed to 1/8.

 

[Yachtsman]

Enter 23.999 and you'll see that the formula's provide here have bugs. This will resolve them:

=MROUND(A1,1/16)/12 & "' " & TEXT(MOD(MROUND(A1,1/16),12),"#-#/##") & """"

Notice the use of the pound sign (#) rather than the question mark (?); this prevents that extra spacing that you wanted removed; the pound will not insert blanks, the question mark will.

The MROUND function requires that the Analysis Tool Pack is installed; it's easy to research how if you don't already have it installed.

This will display zeros for inches and or feet. E.g. 0' 0"" or 0' 1-3/4" or 2' 1/16"

the 1/16 rounds to the nearest 1/16th inch; it's easily changed to 1/8.

Sorry, I forgot to add the INT function. This is correct:

=INT(MROUND(A1,1/16)/12) & "' " & TEXT(MOD(MROUND(A1,1/16),12),"#-#/##") & """"