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