Hello,

I am trying to build a formula to to resolve the following scenario:

Column A Column B
Row 1 $2,000

Row 2 $25,000 $222.22
Row 3 $35,000 $311.11
Row 4 $45,000 $400.00
Row 5 $55,000 $488.89
Row 6 $65,000 $577.78

Row 7 $225,000 $2,000

In this table, A2 through A6 represent salaries (A7 is the total). B1 is
the amount of the bonus pool that is to be divided proportinately to salary.
B2 is calculated as
=(A2/$A$7)*$B$1 (and so on for B3 through B6). B7 represents the total, and
must be equal to B1. This part is all fine.

BUT, what I would like to do is make it so that B2 through B6 have a maximum
possible value of $500.00, with any excess being redistributed over the other
4 rows. So, instead of $577.78, B6 would max at $500.00, and the leftover
$77.78 would be redistributed over B2 through B5. (Which would make
B5$500.00, and have to be redistributed in turn. The total in B7 must
always equal B1.

Is there a formula that can do this for me, without having to do it manually?

Thank you!