Hi, this is my first post in this site.

Glad to be here.

We pack and sell our products by the dozen. So when we create charts
for working with inventories and averages we have to convert things
from and to decimals.

Is there a way or plugging to work with these units in excel?

Here is a simple example

8.06 (8 are doZens, 6 are units)
+5.04
+2.02
=15.12

But it should be 16.

Thanks for your help


--
HDSalinas
------------------------------------------------------------------------
HDSalinas's Profile: http://www.excelforum.com/member.php...o&userid=25131
View this thread: http://www.excelforum.com/showthread...hreadid=386306

one way (assuming your sum of 15.12 is in cell A4). you could enter this
formula in cell A5.

=INT(A4)+INT(MOD(A4,INT(A4))/0.12)+MOD(MOD(A4,INT(A4)),0.12)

"HDSalinas" wrote:


Hi, this is my first post in this site.

Glad to be here.

We pack and sell our products by the dozen. So when we create charts
for working with inventories and averages we have to convert things
from and to decimals.

Is there a way or plugging to work with these units in excel?

Here is a simple example

8.06 (8 are doZens, 6 are units)
+5.04
+2.02
=15.12

But it should be 16.

Thanks for your help


--
HDSalinas
------------------------------------------------------------------------
HDSalinas's Profile: http://www.excelforum.com/member.php...o&userid=25131
View this thread: http://www.excelforum.com/showthread...hreadid=386306

Hi,
Try the following formula:

=INT(SUMPRODUCT(INT(range)+MOD(range,1)*100/12))+MOD(SUMPRODUCT(INT(range)+MOD(range,1)*100/12),1)*0.12

where 'range' stands for the input data range. This formula, I hope, will
convert each dozen:unit value to a decimal value, add all such results, and
finally reconvert the total to the dozen:unit format.

Regards,
B.R. Ramachandran


"HDSalinas" wrote:


Hi, this is my first post in this site.

Glad to be here.

We pack and sell our products by the dozen. So when we create charts
for working with inventories and averages we have to convert things
from and to decimals.

Is there a way or plugging to work with these units in excel?

Here is a simple example

8.06 (8 are doZens, 6 are units)
+5.04
+2.02
=15.12

But it should be 16.

Thanks for your help


--
HDSalinas
------------------------------------------------------------------------
HDSalinas's Profile: http://www.excelforum.com/member.php...o&userid=25131
View this thread: http://www.excelforum.com/showthread...hreadid=386306

The second of the approaches does work. The first that simply attempts to
convert a standard sum will fall over with a larger task.

There is another way to consider - Excel will work with fractions with a
fixed denominator
eg cutom format # ??/12

do the sum using one of the following
=SUM(INT(G18:G23),MOD(G18:G23,1)*100/12) <note must be array entered
=SUMPRODUCT(INT(B18:B23)+MOD(B18:B23,1)*100/12) <as used by B.R.
Ramachandran

format the cell with custom format # ??/12

simpler still is to use the fraction notation for all quantities
so instead of 5.06 use 5 6/12
from this standard arithmatic can be used. Data entry just takes longer

hth RES

Yes, you are absolutely right. Each quantity should be converted to the
decimal format, all the values added, and the decimal total converted back
to the dozen,unit format.
Adding the quantities first (as if they are in the standard decimal format
which they aren't) and converting that (wrong) total back to the dozen:unit
format can lead to very erroneous results, if in the sum of the unit-parts of
the quantities added spills over 99); for example, if there are twelve 1.11
values (i.e., 1 dozen and 11 units) the standard total is 13.32 (a
meaningless total) which when converted to the dozen,unit format would be
15:08 (an erroneous result); the correct result is 23.00 (i.e., 23 dozens and
0 units).

Regards,
B. R. Ramachandran

" wrote:

The second of the approaches does work. The first that simply attempts to
convert a standard sum will fall over with a larger task.

There is another way to consider - Excel will work with fractions with a
fixed denominator
eg cutom format # ??/12

do the sum using one of the following
=SUM(INT(G18:G23),MOD(G18:G23,1)*100/12) <note must be array entered
=SUMPRODUCT(INT(B18:B23)+MOD(B18:B23,1)*100/12) <as used by B.R.
Ramachandran

format the cell with custom format # ??/12

simpler still is to use the fraction notation for all quantities
so instead of 5.06 use 5 6/12
from this standard arithmatic can be used. Data entry just takes longer

hth RES

good point.

I think the following would also address that issue:

If you had 12 rows with 1.11 in them

Cell A13 = {SUM(INT(A1:A12)) + ((SUM(A1:A12)-SUM(INT(A1:A12)))/0.12)}

The formula in A13 would have to be entered as an array formula with
Cntrl+Shift+Enter.


"B. R.Ramachandran" wrote:

Yes, you are absolutely right. Each quantity should be converted to the
decimal format, all the values added, and the decimal total converted back
to the dozen,unit format.
Adding the quantities first (as if they are in the standard decimal format
which they aren't) and converting that (wrong) total back to the dozen:unit
format can lead to very erroneous results, if in the sum of the unit-parts of
the quantities added spills over 99); for example, if there are twelve 1.11
values (i.e., 1 dozen and 11 units) the standard total is 13.32 (a
meaningless total) which when converted to the dozen,unit format would be
15:08 (an erroneous result); the correct result is 23.00 (i.e., 23 dozens and
0 units).

Regards,
B. R. Ramachandran

" wrote:

The second of the approaches does work. The first that simply attempts to
convert a standard sum will fall over with a larger task.

There is another way to consider - Excel will work with fractions with a
fixed denominator
eg cutom format # ??/12

do the sum using one of the following
=SUM(INT(G18:G23),MOD(G18:G23,1)*100/12) <note must be array entered
=SUMPRODUCT(INT(B18:B23)+MOD(B18:B23,1)*100/12) <as used by B.R.
Ramachandran

format the cell with custom format # ??/12

simpler still is to use the fraction notation for all quantities
so instead of 5.06 use 5 6/12
from this standard arithmatic can be used. Data entry just takes longer

hth RES

I see Robert has already posted that formula. Sorry.

"B. R.Ramachandran" wrote:

Yes, you are absolutely right. Each quantity should be converted to the
decimal format, all the values added, and the decimal total converted back
to the dozen,unit format.
Adding the quantities first (as if they are in the standard decimal format
which they aren't) and converting that (wrong) total back to the dozen:unit
format can lead to very erroneous results, if in the sum of the unit-parts of
the quantities added spills over 99); for example, if there are twelve 1.11
values (i.e., 1 dozen and 11 units) the standard total is 13.32 (a
meaningless total) which when converted to the dozen,unit format would be
15:08 (an erroneous result); the correct result is 23.00 (i.e., 23 dozens and
0 units).

Regards,
B. R. Ramachandran

" wrote:

The second of the approaches does work. The first that simply attempts to
convert a standard sum will fall over with a larger task.

There is another way to consider - Excel will work with fractions with a
fixed denominator
eg cutom format # ??/12

do the sum using one of the following
=SUM(INT(G18:G23),MOD(G18:G23,1)*100/12) <note must be array entered
=SUMPRODUCT(INT(B18:B23)+MOD(B18:B23,1)*100/12) <as used by B.R.
Ramachandran

format the cell with custom format # ??/12

simpler still is to use the fraction notation for all quantities
so instead of 5.06 use 5 6/12
from this standard arithmatic can be used. Data entry just takes longer

hth RES

Yes, the array formula (it is very elegant) should also work.
B.R.Ramachandran

"JMB" wrote:

I see Robert has already posted that formula. Sorry.

"B. R.Ramachandran" wrote:

Yes, you are absolutely right. Each quantity should be converted to the
decimal format, all the values added, and the decimal total converted back
to the dozen,unit format.
Adding the quantities first (as if they are in the standard decimal format
which they aren't) and converting that (wrong) total back to the dozen:unit
format can lead to very erroneous results, if in the sum of the unit-parts of
the quantities added spills over 99); for example, if there are twelve 1.11
values (i.e., 1 dozen and 11 units) the standard total is 13.32 (a
meaningless total) which when converted to the dozen,unit format would be
15:08 (an erroneous result); the correct result is 23.00 (i.e., 23 dozens and
0 units).

Regards,
B. R. Ramachandran

" wrote:

The second of the approaches does work. The first that simply attempts to
convert a standard sum will fall over with a larger task.

There is another way to consider - Excel will work with fractions with a
fixed denominator
eg cutom format # ??/12

do the sum using one of the following
=SUM(INT(G18:G23),MOD(G18:G23,1)*100/12) <note must be array entered
=SUMPRODUCT(INT(B18:B23)+MOD(B18:B23,1)*100/12) <as used by B.R.
Ramachandran

format the cell with custom format # ??/12

simpler still is to use the fraction notation for all quantities
so instead of 5.06 use 5 6/12
from this standard arithmatic can be used. Data entry just takes longer

hth RES