# Cell formating and summing imperial weights

1. ## 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  Register To Reply

2. ## Re: Cell formating and summing imperial weights

On Wed, 9 Nov 2005 03:39:09 -0600, Woody <Woody.1ydm2c@no-mx.msusenet.com>
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)

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  Register To Reply