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

[pier4r]

Would it be possible for you to convert the excel formulas (that are harder to read with constant cells like $a$8 and cell operations like ROW) to normal formulas using a1, a2, a3, or whatever? (maybe even without one line if constructs)

This may encourage the discussion.

[Pekis]

(05-24-2019 10:12 AM)pier4r Wrote:  Would it be possible for you to convert the excel formulas (that are harder to read with constant cells like $a$8 and cell operations like ROW) to normal formulas using a1, a2, a3, or whatever? (maybe even without one line if constructs)

This may encourage the discussion.

Well, without Excel, it would give (in a pseudo-BASIC):

ITEM_COUNT=5
DECIMALS=2
DIM A(ITEM_COUNT)
A(1)=83
A(2)=57
A(3)=106
A(4)=87
A(5)=57
NEW_AMOUNT=498

A_SUM=0
FOR I=1 TO ITEM_COUNT
....A_SUM=A_SUM+A(I)
NEXT I...................In the example: A_SUM=390
OLD_AMOUNT=A_SUM

DIM B(ITEM_COUNT)
B_SUM=0
FOR I=1 TO ITEM_COUNT
....B(I)=ROUND(A(I)*(NEW_AMOUNT/OLD_AMOUNT),DECIMALS)
....B_SUM=B_SUM+B(I)
NEXT I...................In the example: B_SUM=497.98

ADJUSTMENT_VALUE=SIGN(NEW_AMOUNT-B_SUM)...................In the example: ADJUSTMENT_VALUE= (+1) (add 1 cent per adjustment)
ADJUSTMENT_COUNT=10^DECIMALS*ABS(NEW_AMOUNT-B_SUM)...................In the example: ADJUSTMENT_COUNT=2 (2 adjustments to do)
ITEMS_PER_ADJUSTMENT=INT(ITEM_COUNT/(ADJUSTMENT_COUNT))...................In the example: ITEMS_PER_ADJUSTMENT=2 (each 2 items: add 1 cent)

DIM C(ITEM_COUNT)
C_SUM=0
FOR I=1 TO ITEM_COUNT
....C(I)=B(I)
....IF (B_SUM<>NEW_AMOUNT) THEN
........IF (I MOD ITEMS_PER_ADJUSTMENT)=0 THEN...................In the example: Each 2 items
............C(I)=B(I)+ADJUSTMENT_VALUE...................In the example: Add 1 cent to the current item
........END IF
....END IF
....C_SUM=C_SUM+C(I)
NEXT I...................In the example: C_SUM=498 and items are adjusted

[Albert Chan]

Hi, Pekis.

Technically, even if distributed amounts added exactly to total, it is still not equitable.
Rounding operation will slightly favored whoever is lucky.

To force distribution of leftover pennies, I would have added them to B1, B3.
When scaled back to $390, we get this: (max abs err = 0.0048, averaged abs err = 0.0036)

Code:

105.99 →  83.0042, err = +0.0042
 72.78 →  56.9964, err = -0.0036
135.36 → 106.0048, err = +0.0048
111.09 →  86.9982, err = -0.0018
 72.78 →  56.9964, err = -0.0036

Pennies to B2, B4 is slightly less equitable: (max abs err = 0.0060, averaged abs err = 0.0041)

Code:

105.98 →  82.9964, err = -0.0036
 72.79 →  57.0042, err = +0.0042
135.35 → 105.9970, err = -0.0030
111.10 →  87.0060, err = +0.0060
 72.78 →  56.9964, err = -0.0036

BTW, why B2 get the penny, but not B5 ? (Both started with the same $57)

[Pekis]

(05-24-2019 12:34 PM)Albert Chan Wrote:  Hi, Pekis.

Technically, even if distributed amounts added exactly to total, it is still not equitable.
Rounding operation will slightly favored whoever is lucky.

To force distribution of leftover pennies, I would have added them to B1, B3.
When scaled back to $390, we get this: (max abs err = 0.0048, averaged abs err = 0.0036)

Code:

105.99 →  83.0042, err = +0.0042
 72.78 →  56.9964, err = -0.0036
135.36 → 106.0048, err = +0.0048
111.09 →  86.9982, err = -0.0018
 72.78 →  56.9964, err = -0.0036

Pennies to B2, B4 is slightly less equitable: (max abs err = 0.0060, averaged abs err = 0.0041)

Code:

105.98 →  82.9964, err = -0.0036
 72.79 →  57.0042, err = +0.0042
135.35 → 105.9970, err = -0.0030
111.10 →  87.0060, err = +0.0060
 72.78 →  56.9964, err = -0.0036

BTW, why B2 get the penny, but not B5 ? (Both started with the same $57)

Hello, Albert,

you're right, in my example, some are favored (but there is no choice, you'll have to favor some entities in the end !), and perhaps more than their own weight, but I wanted initially to get rid of it in Excel with a simple formula, and without having to choose ...

Thanks for the remark.