converting data into dozens

  • Thread starter Thread starter den
  • Start date Start date
D

den

Hi, does anyone know an easy way of converting numerical data so that
it calculates the results in dozens?
For instance, if i wanted to add 3doz 11 and 2doz 3, it will result in
6doz 2.

simple in principle, but have found no one who can come up with a
formula for this.

den
 
With the 3 in A1 formatted as Square Pag suggests to show 3 doz and the 11
in B1 witht he other two quantities in A2 & B2 respectively then use:

=INT((A1*12+B1+A2*12+B2)/12)

to calculate the dozens and

=MOD(A1*12+A2*12+B1+B2,12)

to calculate the remainder

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Hi Den,

First, I'm taking you literally - that is the entries in the cells read 2doz
8. With no space between the 2 and the doz and a space after the doz.
Here is one way - suppose your entries are in cell A1:A100 then in cell B1
enter the formula

=LEFT(A1,FIND("doz ",A1)-1)*12+MID(A1,FIND(" ",A1)+1,2)

Copy this formula down to B100.
In a blank cell enter the following formula:

=INT(SUM(B1:B100)/12)&"doz "&MOD(SUM(B1:B100),12)

Or if you really want to get fancy you can do the whole thing in one cell
with:

=INT(SUMPRODUCT(LEFT(A1:A100,FIND("doz ",A1:A100)-1)*12+MID(A1:A100,FIND("
",A1:A100)+1,2))/12)&"doz "&MOD(SUMPRODUCT(LEFT(A1:A100,FIND("doz
",A1:A100)-1)*12+MID(A1:A100,FIND(" ",A1:A100)+1,2)),12)

Cheers,
Shane Devenshire
Microsoft Excel MVP
 
If you need to add up a column of values, it gets more complicated.

not at all.
In A9
=INT((SUM(A4:A8)*12+SUM(B4:B8))/12)
in B9
=MOD((SUM(A4:A8)*12+SUM(B4:B8)),12)

The ranges can be as large as you like, it makes no difference to the
calculation.
 
Bonsour® den avec ferveur ;o))) vous nous disiez :
Hi, does anyone know an easy way of converting numerical data so that
it calculates the results in dozens?
For instance, if i wanted to add 3doz 11 and 2doz 3, it will result in
6doz 2.

simple in principle, but have found no one who can come up with a
formula for this.

den
Click to expand...

Selection.NumberFormat = "#"" ""??/12"
i.e.
A B C D
=A1 =INT(A1) =MOD(A1,1)
13,15 13 2/12 13 2/12
4,87 4 10/12 4 10/12
6,98 7 6 1
11,54 11 7/12 11 7/12
18,86 18 10/12 18 10/12
12,79 12 9/12 12 9/12
7,29 7 4/12 7 4/12
12,84 12 10/12 12 10/12

SUM
88,33 88 4/12 83 5 4/12


HTH
 
Back
Top