Results 1 to 4 of 4

Thread: Excel Reallocation until a desireable outcome

  1. #1

    Excel Reallocation until a desireable outcome

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Excel 2010 Tables
    With Goal Seek
    Attached Files Attached Files

  3. #3


    Quote Originally Posted by Herbds7 View Post
    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.

    Attached Files Attached Files

  4. #4
    Same method, just change the formula a bit.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts