Payout Calculation

For the payout of credits of multiple people over time, i wanted to setup a table where the “Available Payout Amount” is divided through the “Current Number Of People”. So everybody gets the same, unless its the last installment of a person and the amount is lower than the Divided Payout Amount. That’s where i’d need to use the difference of unused amounts for last installments and distribute it to the others again.

How is that being done ? I manually calculated the values in the following screenshot. A sample .xls for Excel 2003 would be very cool.

2014_01_06_23_26_10.jpg

Regards,
Frank

Hi ProtonLeah,
do you mean i should attach the manually created table ?

Regards,
Frank

ok thx, have attatched it.

k thx, yea i was thinking and thinking about how to determine who has a rest amount and how to trigger to re-calculate the other people new and how to find out how many they are without additional columns, and came to the conclusion that it probably needs VBA to solve such a thing.

Hi Isandol, sure its ok, just was thinking if necessary. But yea the balances by each payment are good extra info for viewing anyways.

Thx

Best I've come up with is:
=IF(2*$B2-SUM($B2:B2)<=0,0,IF(2*$B2-SUM($B2:B2)<=C$6/IF(COLUMN(B2)=2,3,COUNTIF(B$2:B$4,MAX(B$2:B$4))),2*$B2-SUM($B2:B2),(C$6-IF(2*$B$2:$B$4-CHOOSE(ROW($A$1:$A$3),SUM($B$2:B$2),SUM($B$3:B$3),SUM($B$4:B$4))<=0,0,SUM(IF(2*$B$2:$B$4-CHOOSE(ROW($A$1:$A$3),SUM($B$2:B$2),SUM($B$3:B$3),SUM($B$4:B$4))<C$6/IF(COLUMN(B2)=2,3,COUNTIF(B$2:B$4,MAX(B$2:B$4))),2*$B$2:$B$4-CHOOSE(ROW($A$1:$A$3),SUM($B$2:B$2),SUM($B$3:B$3),SUM($B$4:B$4)),0))))/SUM(IF(2*$B$2:$B$4-CHOOSE(ROW($A$1:$A$3),SUM($B$2:B$2),SUM($B$3:B$3),SUM($B$4:B$4))>C$6/IF(COLUMN(B2)=2,3,COUNTIF(B$2:B$4,MAX(B$2:B$4))),1,0))))

ok thx, does the solution allow to add / remove more people ?

sort of, to extend to four would be:
=IF(2*$B2-SUM($B2:B2)<=0,0,IF(2*$B2-SUM($B2:B2)<=C$6/IF(COLUMN(B2)=2,3,COUNTIF(B$2:B$5,MAX(B$2:B$5))),2*$B2-SUM($B2:B2),(C$6-IF(2*$B$2:$B$5-CHOOSE(ROW($A$1:$A$4),SUM($B$2:B$2),SUM($B$3:B$3),SUM($B$4:B$4),SUM($B$5:B$5)<=0,0,SUM(IF(2*$B$2:$B$5-CHOOSE(ROW($A$1:$A$4),SUM($B$2:B$2),SUM($B$3:B$3),SUM($B$4:B$4),SUM($B$5:B$5)<C$6/IF(COLUMN(B2)=2,3,COUNTIF(B$2:B$5,MAX(B$2:B$5))),2*$B$2:$B$5-CHOOSE(ROW($A$1:$A$4),SUM($B$2:B$2),SUM($B$3:B$3),SUM($B$4:B$4),SUM($B$5:B$5),0))))/SUM(IF(2*$B$2:$B$5-CHOOSE(ROW($A$1:$A$4),SUM($B$2:B$2),SUM($B$3:B$3),SUM($B$4:B$4),SUM($B$5:B5)>C$6/IF(COLUMN(B2)=2,3,COUNTIF(B$2:B$5,MAX(B$2:B$5))),1,0))))

I think.

The problem would be that as it stands the choose() function needs Sum($B$2:B$2),Sum($B$3:B$3) writing out for every one individually so if you ahd say 20 names, the formula would become too large. You would need another way of constructing the array created by the choose function to use it with a lot of names.

ya i came to the same conclusion, that IF chains are not dynamic and limited in capacity. Unfortunately i have not near enough knowledge for VBA to handle such a script.

How many payment columns are there likely to be, it might be possible to rearrange it so the number of people is dynamic and the payment columns are not.

ok, yes it can be hundreds of people.

I was asking how many numbers of payouts there were likely to be....

ah ok, infinite number ... basically ongoing.

wow that works like magic ! need some time to figure out how it works.
Thank you :-)