I have the following table set up
Limit Allocation Yield Ranking
$600 [to calc] 0.07% 7
$600 0.09% 6
$600 0.20% 1
$400 0.20% 1
$400 0.13% 4
$200 0.19% 3
$200 0.12% 5
Additionally, I have a constraint which I could only allocate a total of $2000 across the 7 rows here, by the rankings of their yield (so a higher yield would get everything allocated up to the limit column if there is any left overs from the $2000 total).
I was wondering how I could set up the equations so that it could perform the allocation automatically. Thanks!
I'm going to assume this table starts in A1...
In E1, put the amount you have to allocate
In B2 (and then copied to B3...B8) use the following formula
=MIN(A2,$E$1-SUMIF($D$2:$D$8,">"&D2,$B$2:$B$8))
This will work out how much has been taken by higher ranked, and take the rest, upto whatever is the lesser amount of their limit, and what is left in the pot.
There is one fault with this equation that you will need to figure out how to handle:
If there are equal ranks at the end of the distribution, then both will get the final amount. (e.g. try this with $2,001, and you will see that the 2 rows that have then rank 1 will both claim the final dollar)