# formula to convert decimal feet to feet inch sixteenths

1. ## formula to convert decimal feet to feet inch sixteenths

I would like to write a formula in excel, without using macros, to convert
decimal feet to feet-inch-sixteenths.

Can someone help me ?  Register To Reply

2. ## Re: formula to convert decimal feet to feet inch sixteenths

Is this any good?

=TEXT(INT(D18)," 0 ""feet """)&INT(MOD(D18,1)*12)&"
"&TEXT(MOD(D18*12,1),"0/16")&" inches"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"tv" <tv@discussions.microsoft.com> wrote in message
news:519E2C11-5977-4479-A573-5C7470DBB769@microsoft.com...
> I would like to write a formula in excel, without using macros, to convert
> decimal feet to feet-inch-sixteenths.
>
> Can someone help me ?  Register To Reply

3. ## Re: formula to convert decimal feet to feet inch sixteenths

"tv" <tv@discussions.microsoft.com> wrote in message
news:519E2C11-5977-4479-A573-5C7470DBB769@microsoft.com...
>I would like to write a formula in excel, without using macros, to convert
> decimal feet to feet-inch-sixteenths.
>
> Can someone help me ?

Are you able to write the formula on paper first?  Register To Reply

4. ## RE: formula to convert decimal feet to feet inch sixteenths

Decimal value in A1, formula in B1:

=INT(A1) & " ft " & TEXT(MOD(A1,1)*12, "# ??/16") & "in"

A1=2.345 (ft)

B1=2 ft 4 2/16 in

HTH

"tv" wrote:

> I would like to write a formula in excel, without using macros, to convert
> decimal feet to feet-inch-sixteenths.
>
> Can someone help me ?  Register To Reply

5. ## Re: formula to convert decimal feet to feet inch sixteenths

Whilst I concede that yours is much better than mine, it can still be
improved upon <vbg>

=INT(A1) & " ft " & TEXT(MOD(A1,1)*12, "# ??/16 ""in""")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Toppers" <Toppers@discussions.microsoft.com> wrote in message
news:94F33E54-AB05-4FA2-BDA2-A95800A648C5@microsoft.com...
> Decimal value in A1, formula in B1:
>
>
> =INT(A1) & " ft " & TEXT(MOD(A1,1)*12, "# ??/16") & "in"
>
> A1=2.345 (ft)
>
> B1=2 ft 4 2/16 in
>
> HTH
>
> "tv" wrote:
>
>
> > I would like to write a formula in excel, without using macros, to

convert
> > decimal feet to feet-inch-sixteenths.
> >
> > Can someone help me ?  Register To Reply

6. ## Re: formula to convert decimal feet to feet inch sixteenths

Bob,
I'm flattered!

"Bob Phillips" wrote:

> Whilst I concede that yours is much better than mine, it can still be
> improved upon <vbg>
>
> =INT(A1) & " ft " & TEXT(MOD(A1,1)*12, "# ??/16 ""in""")
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> news:94F33E54-AB05-4FA2-BDA2-A95800A648C5@microsoft.com...
> > Decimal value in A1, formula in B1:
> >
> >
> > =INT(A1) & " ft " & TEXT(MOD(A1,1)*12, "# ??/16") & "in"
> >
> > A1=2.345 (ft)
> >
> > B1=2 ft 4 2/16 in
> >
> > HTH
> >
> > "tv" wrote:
> >
> >
> > > I would like to write a formula in excel, without using macros, to

> convert
> > > decimal feet to feet-inch-sixteenths.
> > >
> > > Can someone help me ?

>
>
>  Register To Reply

7. ## RE: formula to convert decimal feet to feet inch sixteenths

Thamk you for this formula
however, i would like my result to read in the following format (ex: 4-6-8)

can you help me tune the formula

thanks

"Toppers" wrote:

> Decimal value in A1, formula in B1:
>
>
> =INT(A1) & " ft " & TEXT(MOD(A1,1)*12, "# ??/16") & "in"
>
> A1=2.345 (ft)
>
> B1=2 ft 4 2/16 in
>
> HTH
>
> "tv" wrote:
>
>
> > I would like to write a formula in excel, without using macros, to convert
> > decimal feet to feet-inch-sixteenths.
> >
> > Can someone help me ?  Register To Reply

8. ## Re: formula to convert decimal feet to feet inch sixteenths

On Tue, 7 Mar 2006 13:27:26 -0800, tv <tv@discussions.microsoft.com> wrote:

>Thamk you for this formula
>however, i would like my result to read in the following format (ex: 4-6-8)
>
>can you help me tune the formula
>
>thanks

Here's one way:

=INT(A1)&"-"&INT(MOD(A1,1)*12)&"-"&ROUND(MOD(MOD(A1,1)*12,1)*16,0)

--ron  Register To Reply

9. ## Re: formula to convert decimal feet to feet inch sixteenths

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

The formula above display inches with a decimal to feet and inches with a fraction to the nearest 16th; a great test for many formula's is to enter 23.999; above it will properly display 2' 0"

The formula will display zero for feet and/or inches. e.g. 0' 0", or 2' 0", or 0' 1/16"

other display examples would be like this: 1' 2-3/16"

The MROUND function requires the Analysis Tool Pack addin to be installed; it's easy to research how if it's not installed.  Register To Reply

10. ## Re: formula to convert decimal feet to feet inch sixteenths

Can you tell me how to do it and have it look like this (26") 2'-2" or 26" (2'-2")  Register To Reply

11. ## Re: formula to convert decimal feet to feet inch

Can you tell me how to do it and have it look like this (26") 2'-2" or 26" (2'-2")

in that same cell I am summing up first for example- =SUM(B14*K14) then how do you write it like above?
Thx  Register To Reply

12. ## Re: formula to convert decimal feet to feet inch sixteenths

So here is a curve ball to this thread.

What about having it function properly with negative values?

I have values for charting out points on graph paper for easy explanation. Simply there is -x and -y values. The formula seems to function correctly for the non negative values but seems to work incorrectly when there is a negative value.

Quick example being I have a line going left to right one point is at -9.833, next is at 0, next is at 9.833. The first value returns as -10ft 2 in then 0 ft 0 in, then 9 ft 10 in.

Any insight?

Thanks,
Matt  Register To Reply

13. ## Re: formula to convert decimal feet to feet inch sixteenths Originally Posted by MattG So here is a curve ball to this thread.

What about having it function properly with negative values?

I have values for charting out points on graph paper for easy explanation. Simply there is -x and -y values. The formula seems to function correctly for the non negative values but seems to work incorrectly when there is a negative value.

Quick example being I have a line going left to right one point is at -9.833, next is at 0, next is at 9.833. The first value returns as -10ft 2 in then 0 ft 0 in, then 9 ft 10 in.

Any insight?

Thanks,
Matt
Matt, firstly, this thread is over 5 years old, I doubt many are still following it.

Welcome to the forum. We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread  Register To Reply