Apportionment

05242019, 09:14 AM
(This post was last modified: 05242019 11:46 AM by Pekis.)
Post: #1




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 

05242019, 10:12 AM
(This post was last modified: 05242019 10:12 AM by pier4r.)
Post: #2




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 :) 

05242019, 12:26 PM
(This post was last modified: 05242019 12:34 PM by Pekis.)
Post: #3




RE: Apportionment
(05242019 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) Well, without Excel, it would give (in a pseudoBASIC): 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_AMOUNTB_SUM)...................In the example: ADJUSTMENT_VALUE= (+1) (add 1 cent per adjustment) ADJUSTMENT_COUNT=10^DECIMALS*ABS(NEW_AMOUNTB_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 

05242019, 12:34 PM
Post: #4




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 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 BTW, why B2 get the penny, but not B5 ? (Both started with the same $57) 

05242019, 12:42 PM
(This post was last modified: 05242019 02:14 PM by Pekis.)
Post: #5




RE: Apportionment
(05242019 12:34 PM)Albert Chan Wrote: Hi, Pekis. 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: