+ Reply to Thread
Results 1 to 13 of 13

formula to convert decimal feet to feet inch sixteenths

  1. #1
    tv
    Guest

    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 ?

  2. #2
    Bob Phillips
    Guest

    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 ?




  3. #3
    Doug Kanter
    Guest

    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?



  4. #4
    Toppers
    Guest

    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 ?


  5. #5
    Bob Phillips
    Guest

    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 ?




  6. #6
    Toppers
    Guest

    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 ?

    >
    >
    >


  7. #7
    tv
    Guest

    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 ?


  8. #8
    Ron Rosenfeld
    Guest

    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

  9. #9
    Registered User
    Join Date
    06-29-2011
    Location
    Fort Lauderdale
    MS-Off Ver
    Excel 2018
    Posts
    23

    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.
    Last edited by theclockmaker; 10-10-2012 at 11:46 AM.

  10. #10
    Registered User
    Join Date
    01-13-2015
    Location
    davenport ia
    MS-Off Ver
    2013
    Posts
    2

    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")

  11. #11
    Registered User
    Join Date
    01-13-2015
    Location
    davenport ia
    MS-Off Ver
    2013
    Posts
    2

    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

  12. #12
    Registered User
    Join Date
    10-28-2008
    Location
    Chicago
    Posts
    69

    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

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,356

    Re: formula to convert decimal feet to feet inch sixteenths

    Quote Originally Posted by MattG View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1