Adding & Subtracting lbs ozs & drms

[john.bedford3]

Can anyone suggest a simpler way of adding and subtracting lbs ozs & drms.
An example of how I am calculating these at the moment is shown below. Rows
8, 9 and 12 being extra rows to help calculation.

A B C
1 lbs ozs drms
2 12 9 6.7
3 7 6 12.2
4 9 14 15.9
5 7 2 5.3
6 18 4 2.8
7 15 9 11.2
=============
8 32 48
9 2 3
=============
10 70 15 6.1 (Sum of rows 1 to 7)
11 66 8 15.9
=============
12 9
=============
13 4 6 6.2 ( Row 10 minus Row 11)
=============

The formulas I am using are as follows:-

R8 ColC =(TRUNC(SUM(C2:C7)/16)*16)
R8 ColB =(TRUNC((SUM(B2:B7)+C9)/16)*16)
R9 ColC =C8/16
R9 ColB =B8/16
R10 Col C =ROUND(SUM(C2:C7)-C8,1)
R10 Col B =(SUM(B2:B7)+C9)-B8
R10 Col A =SUM(A2:A7)+B9
R12 Col B =IF(C11>C10,B11+1,B11)
R13 Col C =IF(C11>C10,(C10+16)-C11,C10-C11)
R13 Col B =IF(B12>B10,(B10+16-B12),B10-B12)
R13 Col A =IF(B12>B10,A10-(A11+1),A10-A11)

 

[Ron Rosenfeld]

Can anyone suggest a simpler way of adding and subtracting lbs ozs & drms.

I don't know if it's simpler, but:

Name your ranges lbs, ozs, and drms. E.g.
lbs: A2:A7
ozs: B2:B7
drms: C2:C7

To sum up the weights with the result in drams, you can use the
**array-formula**:

=SUM(lbs*16*16,ozs*16,drms)

To enter an **array** formula, after typing it in, hold down <ctrl><shift>
while hitting <enter>. XL will place braces {...} around the formula.

Put that formula in, let us say, E2

To convert that result back to lbs, ozs and drams, use the following formulas:

lbs: =INT(E2/16/16)
ozs: =INT(MOD(E2,16*16)/16)
drams: =MOD(E2,16)

-------------------------------------

Or, if you do not want to use the intermediate step with the formula in E2, use
the following **array** formulas:

lbs: =INT(SUM(lbs*16*16,ozs*16,drms)/16/16)
ozs: =INT(MOD(SUM(lbs*16*16,ozs*16,drms),16*16)/16)
drms: =MOD(SUM(lbs*16*16,ozs*16,drms),16)


----------------------------

If you want a different sort of output, let us know.







--ron

 

[john.bedford3]

Thanks Ron,

As I have several sets of values to add on each sheet in my workbook, I have
used the formulas without the intermediate step and this has worked fine for
adding the weights and with a small alteration also for subtracting.

This will tidy up things a lot as I can get rid of the extra rows and
calculations.

Thanks once again, your help is greatly appreciated.

 

[Ron Rosenfeld]

You're welcome, John

Glad to help. Thank you for the feedback.

Best,

Thanks Ron,

As I have several sets of values to add on each sheet in my workbook, I have
used the formulas without the intermediate step and this has worked fine for
adding the weights and with a small alteration also for subtracting.

This will tidy up things a lot as I can get rid of the extra rows and
calculations.

Thanks once again, your help is greatly appreciated.

--ron