I would like to write a formula in excel, without using macros, to convert
decimal feet to feet-inch-sixteenths.
Can someone help me ?
I would like to write a formula in excel, without using macros, to convert
decimal feet to feet-inch-sixteenths.
Can someone help me ?
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 ?
"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?
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 ?
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 ?
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 ?
>
>
>
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 ?
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
=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.
Last edited by theclockmaker; 10-10-2012 at 11:46 AM.
Can you tell me how to do it and have it look like this (26") 2'-2" or 26" (2'-2")
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
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.
Secondly, Administrative Note:
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.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks