Excel Reallocation until a desireable outcome

**dan23nyc** · 2013-11-27, 02:37 AM

I am stuck. I don't know if there is a function or a macro to help with my situation. I need to do this for thousands of row. Ex. I have 100 employee that all work different amount of hours.. I then have a bonus fund that needs to be allocated based on the percentage of hours works to total hours. However each bonus has to be equal or over a specific value (a predetermined cell aka X). My columns are as follow: (A) Employee, (B)Hours worked, (C) bonus amount (=B/Total Hours*total Bonus). I need to take these columns and turn them into a new report for these employees where the minimum bonus is X and all non allocated bonus is reallocated to remaining employees. The manual process is to copy these columns and formulas and then remove each row starting from the lowest amount under X. Each time one row is removed the other amounts above adjust and I keep repeating this until the lowest bonus is equal to or greater than X. Is there a way to do this in one sweep if I determine what X should be? and if they rerun using different values for X? THANK YOU

**Herbds7** · 2013-11-27, 04:48 PM

Excel 2010 Tables
With Goal Seek

**dan23nyc** · 2013-11-27, 05:39 PM

Originally Posted by Herbds7

Excel 2010 Tables
With Goal Seek

Thank you for your time Herbds7

The output is not what i was looking for.
Say the minimum bonus i can distribute is $100 or a value that can change, say L7 in you spreadsheet.
What i would need to do is delete the lowest bonus, so that the L6 or the 20000 gets reallocated to the remaining employees on a proportionate amount.
Then check the last payment if less than L7 then repeat, and so on until the lowest payment is less than or equal to L7.
Since the reallocation of bonuses impacts each remaining employee on a proportionate lever, it must be done one by one.

I've reattached the file with what should be the revised output.

THANK YOU AGAIN FOR YOUR TIME AND HELP