Apportionment

[Pekis]

Perhaps it will be interesting for you if you ever redistributed an amount already shared between entities and had to deal with the following constraints:

- The sum of the parts has to be equal to the new amount distributed
- The part of each entity has to be rounded to a fixed number of decimals
- Due to this rounding, the readjustments must be spreaded over the entities with an equal and smallest amount as needed

If it's about money, you often end up with cents left or missing (due to rounding) and you need a formula so you don't care about the readjustements.

Example:
Current distribution (put in the cells A1:A6 in Excel):
A1: 83.00
A2: 57.00
A3: 106.00
A4: 87.00
A5: 57.00
A6: =SUM(A1:A5) giving $390.00)

If the new amount to distribute is $498.00 (put in $A$8), the first calculation coming to the mind is:

B1: =ROUND(A1*($A$8/$A$6),2) giving $105.98
B2: =ROUND(A2*($A$8/$A$6),2) giving $72.78
B3: =ROUND(A3*($A$8/$A$6),2) giving $135.35
B4: =ROUND(A4*($A$8/$A$6),2) giving $111.09
B5: =ROUND(A5*($A$8/$A$6),2) giving $72.78
B6: =SUM(B1:B5) giving $497.98

But this leads to a total of $497.98 (in B6) instead of $498.00
Of course, you could imagine filling up the last item with 2 cents and we are done, but this is not equitable.
So, the objective is adjust a value by at most 1 cent all over the rows when it's needed.

I think this is the cure for the disease (with a third column)(and for a number of decimals equal to 2):
C1: =B1+IF($A$8<>$B$6,IF(MOD(ROW(B1)-MIN(ROW(B$1:B$6))+1,INT(ROWS(B$1:B$6)/(10^2*ABS($A$8-$B$6))))=0,SIGN($A$8-$B$6)/10^2,0),0) giving $105.98
C2: =B2+IF($A$8<>$B$6,IF(MOD(ROW(B2)-MIN(ROW(B$1:B$6))+1,INT(ROWS(B$1:B$6)/(10^2*ABS($A$8-$B$6))))=0,SIGN($A$8-$B$6)/10^2,0),0) giving $72.79 (NEW)
C3: =B3+IF($A$8<>$B$6,IF(MOD(ROW(B3)-MIN(ROW(B$1:B$6))+1,INT(ROWS(B$1:B$6)/(10^2*ABS($A$8-$B$6))))=0,SIGN($A$8-$B$6)/10^2,0),0) giving $135.35
C4: =B4+IF($A$8<>$B$6,IF(MOD(ROW(B4)-MIN(ROW(B$1:B$6))+1,INT(ROWS(B$1:B$6)/(10^2*ABS($A$8-$B$6))))=0,SIGN($A$8-$B$6)/10^2,0),0) giving $111.10 (NEW)
C5: =B5+IF($A$8<>$B$6,IF(MOD(ROW(B5)-MIN(ROW(B$1:B$6))+1,INT(ROWS(B$1:B$6)/(10^2*ABS($A$8-$B$6))))=0,SIGN($A$8-$B$6)/10^2,0),0) giving $72.78
C6: =SUM(C1:C5) giving $498.00

(you can replace 2 by another number of decimals in the formulas if you want but round with that number in the column B)

This time, I'm happy (but, I know, 2 columns are needed instead of one)

The idea behind the formula is to
- Calculate the left or missing cents: 10^2*ABS($A$8-$B$6), here 100*($498.00-$497.998) = +2 => 2 cents left
- Spread evenly those cents over the rows, so calculate the number of rows between each adjustment of at most 1 cent: INT(ROWS(B$1:B$6)/(10^2*ABS($A$8-$B$6))), here INT(5 rows / 2 adjustments)=2 => adjust with 1 cent for each 2 rows
- You can then add (or subtract, according to the sign of ($A$8-$B$6)) a cent each time the modulo of the row number (ROW(B1)-MIN(ROW(B$1:B$6))+1) by the number of rows between each adjustment equals to 0

It seems to work well.

What do you think of it ?

Pekis