Way over my head -- is this possible?

[zcg32]

I'm trying to figure out a way to do the following, and forgive me if I'm posting something entirely too complicated. Sorry for any time wasted. But if you want to help....

Lets assume I have a potential pool of 500 employees. Each employee has a productivity score, and a salary.

The total salary I can pay is a max of $100 million. The total employees I can, and must hire, is exactly 12.

My goal is to maximize total productivity score of these 12 hires, and there is no benefit for paying out less than $100 million.

What I want to do is take my list of 500 names, along with each of their respective salaries and productivity scores, and rank every single 12-person combination, according to total productivity score.

Lets say there are 1 million combinations -- the combination with the highest total productivity would be worth 1 million. The combination with the next highest would be 999,999, and on down the line. After that runs, you can simply add up each employees total points to find out who is the most valuable employee under the "exactly 12 employee, spend up to $100 million" constraint.

Does that make sense? Is there another way to think about this problem?

Thanks so much!

 

[forexcel]

This can be done using VBA code not formulas. Although the possible combinations are extremely too high number and you will might have to wait months to get results. The combinations of 3 out of 500 are more than 20 millions

 

[zcg32]

Thanks so much for the reply. So Excel is probably not going to cut it here. Thanks again.

 

[forexcel]

Thanks so much for the reply. So Excel is probably not going to cut it here. Thanks again.

It can be done in excel using VBA but as I said the combinations are too many so practically you will not have results soon in any language you try. There are something 2e31 (2 following with 31 0s at end) combinations

 

[Macropod]

Lets assume I have a potential pool of 500 employees. Each employee has a productivity score, and a salary.

The total salary I can pay is a max of $100 million. The total employees I can, and must hire, is exactly 12.

My goal is to maximize total productivity score of these 12 hires, and there is no benefit for paying out less than $100 million.

What I want to do is take my list of 500 names, along with each of their respective salaries and productivity scores, and rank every single 12-person combination, according to total productivity score.
…
the combination with the highest total productivity would be worth 1 million.

It seems to me that is doesn't matter which 12 employees you pick, the total package paid will never exceed 1 million of the available 100 million. Accordingly, there is no point in ranking the combinations - simply pick the 12 highest-ranked names.

 

[=ODIN=]

Are you trying to automate a way to solve this problem over and over again for a bunch of different 500 employee datasets, or just the once for a specific 500 employee dataset?

For me, I'd start to answer this by calculating a (PPD) Productivity Per Dollar value (Productivity Score / Salary) for each employee. Then I'd sort the 500 employee list on PPD descending, and Salary ascending -- basically a list showing me first those employees with the most productivity with the smallest salaries. From there I'd see do a bit of analysis -- if I'm lucky, the first 12 emplyoyees on such a list will make less than $100M and I'm good to go. Otherwise, I do a little bit more analysis -- but always maximize taking as many people from the highest productivity bucket for the cheapest.