Redistribution of values after reaching maximum

[DG]

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!

 

[Paul C]

Start in B6 and use
=MIN((A6/$A$7)*$B$1,500)

For B5 use
=MIN((A5/($A$7-SUM(A6:$A$6)))*($B$1-SUM(B6:$B$6)),500) and copy up

B4 becomes =MIN((A4/($A$7-SUM(A5:$A$6)))*($B$1-SUM(B5:$B$6)),500)
B3 becomes ==MIN((A3/($A$7-SUM(A4:$A$6)))*($B$1-SUM(B4:$B$6)),500)
etc...

 

[Joe User]

So, instead of $577.78, B6 would max at $500.00,
and the leftover $77.78 would be redistributed over
B2 through B5.

This is easier to formulate if you move row 7 to row 8, leaving row 7 empty.
You can hide row 7, if you wish.

Then put the following in B6 and copy into B2:B5:

=MIN(500,
ROUND(MAX(0, $B$1-SUM(B7:$B$7))
*A6/SUM($A$2:A6), 2))

The use of ROUND(...,2) and MAX(0,...) accounts for the real-world
constraint that payments must be rounded to pennies (at least), so the
percentages may result in quantization "errors".


----- original message -----

 

[Joe User]

So, instead of $577.78, B6 would max at $500.00,
and the leftover $77.78 would be redistributed over
B2 through B5.

The formula I presented implements what you described, I think. But is that
really fair?

Consider the case when the bonus pool is $2500. Everyone receives $500
(20%), including A2, whose salary ($25,000) represents only 11.11% and who
would normally get a bonus of $277.75.

I would think a more fair rule is that everyone gets a proportion of the
bonus pool relative to their proportion of the salary pool up to $500. That
formula is simply:

=MIN(500,
$B$1-SUM(B7:$B$7),
ROUND($B$1*A6/$A$8,2))


----- original message -----

 

[DG]

Thank you very much! The formula does indeed implement what I am looking for.

With regards to your concern over fairness, I must say that the situation
is, in fact, more complex than the one I described. For example, the
salaries are not actual salaries but rather "eligible" salaries, which are
derived from a calculation that considers years of service. The formula
that calculates "eligible" salary is straightforward and functions exactly as
I want it to, so I left this out of the description of the scenario in order
to simplify the explanation (Column A of my example is actually Column K of a
larger spreadsheet).

While your second proposed formula is indeed simpler (and more fair!), it
does not take into account that the total bonus paid out MUST total the
amount of the bonus pool. Thus, if the bonus pool was $2,500 as you
suggested, each of the 5 would have to get $500; anything else would not
total $2,500. (Which, of course, opens the question of what would happen if
the bonus pool was more than $2,500 and the $500 max remains in place, but
that is a dilemma for another day.)

It may not be fair, but it is the policy in place. I did not design it, I
am just looking for a faster, more efficient way of implementing it, and you
have helped me do that. Thank you again!