+ Reply to Thread
Results 1 to 2 of 2

Cell formating and summing imperial weights

  1. #1
    Woody
    Guest

    Cell formating and summing imperial weights


    Hi all,

    Does anybody know how to format cells for using imperial weights
    (pounds, ounces and drams) and how do I then summ these values to also
    give the result in pounds, ounces and drams?


    --
    Woody
    ------------------------------------------------------------------------
    Woody's Profile: http://www.msusenet.com/member.php?userid=5757
    View this thread: http://www.msusenet.com/t-1871176935


  2. #2
    Ron Rosenfeld
    Guest

    Re: Cell formating and summing imperial weights

    On Wed, 9 Nov 2005 03:39:09 -0600, Woody <[email protected]>
    wrote:

    >
    >Hi all,
    >
    >Does anybody know how to format cells for using imperial weights
    >(pounds, ounces and drams) and how do I then summ these values to also
    >give the result in pounds, ounces and drams?


    I don't believe you can do that with formatting.

    Probably the simplest method is to either enter your data in three separate
    cells; define a separator and enter it as a text string; or use a custom form
    that takes care of the conversion.

    Then store the data as units and fractions of units of one of the above.

    Finally, convert it and display it.

    For example, to enter 6lb 4oz 11dr you could enter the data as 6.04.11 or
    6.4.11.

    Then have a separate cell where you convert the result to drams:

    The formula for that would be:

    =LEFT(A1,FIND(".",A1)-1)*16*16+MID(A1,FIND(
    ".",A1)+1,FIND(CHAR(1),SUBSTITUTE(A1,".",
    CHAR(1),2))-FIND(".",A1))*16+MID(A1,FIND(
    CHAR(1),SUBSTITUTE(A1,".",CHAR(1),2))+1,5)

    or, if you download and install Longre's free morefunc.xll add-in from
    http://xcell05.free.fr/, the simpler:

    =REGEX.MID(A1,"\d+",1)*16*16+REGEX.MID(A1,"\d+",2)*16+REGEX.MID(A1,"\d+",3)

    You can then SUM the values in drams, and convert them back for display:

    If the SUM is in B4, then:

    =INT(B4/16/16)&"."&INT(MOD(B4,16*16)/16)&"."&MOD(B4,16)


    --ron

+ 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