If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Redistribution of values after reaching maximum
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! |
#2
|
|||
|
|||
Redistribution of values after reaching maximum
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... -- If this helps, please remember to click yes. "DG" wrote: 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! |
#3
|
|||
|
|||
Redistribution of values after reaching maximum
"DG" wrote:
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 ----- "DG" wrote: 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! |
#4
|
|||
|
|||
Redistribution of values after reaching maximum
"DG" wrote:
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 ----- "Joe User" wrote: "DG" wrote: 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 ----- "DG" wrote: 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! |
#5
|
|||
|
|||
Redistribution of values after reaching maximum
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! "Joe User" wrote: "DG" wrote: 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 ----- "Joe User" wrote: "DG" wrote: 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 ----- "DG" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|