Adding & Subtracting lbs ozs & drms

  • Thread starter Thread starter john.bedford3
  • Start date Start date
J

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)
 
Can anyone suggest a simpler way of adding and subtracting lbs ozs & drms.
Click to expand...

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
 
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.
 
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.
Click to expand...

--ron
 
Back
Top