Apportionment
05-24-2019, 09:14 AM (This post was last modified: 05-24-2019 11:46 AM by Pekis.)
Post: #1
 Pekis Member Posts: 111 Joined: Aug 2014
Apportionment
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
05-24-2019, 10:12 AM (This post was last modified: 05-24-2019 10:12 AM by pier4r.)
Post: #2
 pier4r Senior Member Posts: 2,020 Joined: Nov 2014
RE: Apportionment
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.

Wikis are great, Contribute :)
05-24-2019, 12:26 PM (This post was last modified: 05-24-2019 12:34 PM by Pekis.)
Post: #3
 Pekis Member Posts: 111 Joined: Aug 2014
RE: Apportionment
(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

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
........END IF
....END IF
....C_SUM=C_SUM+C(I)
NEXT I...................In the example: C_SUM=498 and items are adjusted
05-24-2019, 12:34 PM
Post: #4
 Albert Chan Senior Member Posts: 967 Joined: Jul 2018
RE: Apportionment
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)
05-24-2019, 12:42 PM (This post was last modified: 05-24-2019 02:14 PM by Pekis.)
Post: #5
 Pekis Member Posts: 111 Joined: Aug 2014
RE: Apportionment
(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.
 « Next Oldest | Next Newest »

User(s) browsing this thread: 1 Guest(s)