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 

« Next Oldest  Next Newest »

Messages In This Thread 
Apportionment  Pekis  05242019 09:14 AM
RE: Apportionment  pier4r  05242019, 10:12 AM
RE: Apportionment  Pekis  05242019, 12:26 PM
RE: Apportionment  Albert Chan  05242019, 12:34 PM
RE: Apportionment  Pekis  05242019, 12:42 PM

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