List all possible combinations of 6 IF the sum is between a certain range

[jor2511]

I have a list of employees, their salaries and ratings. I need to display all possible combinations of groups of 6 employees but the sum of their salaries needs to be between 49000-50000. If the sums of the salaries are too high or low, i don't want a list to be returned. I then want to return the average rating of each group. Below is my data set and an example of what I am looking for.

Employee #	Salary	Rating
1	12500	95.711
2	11700	82.1
3	11000	88.167
4	10500	85.867
5	10300	73.05
6	9800	73.632
7	9100	76.026
8	8700	85.806
9	8500	71.375
10	8000	77.167
11	7900	66.333
12	7800	75.521
13	7700	60.711
14	7600	75.019
15	7500	66.5
16	7400	68.043
17	7300	62.87
18	7300	67.26
19	7300	69.621
20	7300	76.357
21	7200	65.115
22	7200	62.19
23	7200	57.85
24	7100	65.48
25	7100	59.019
26	7000	62.457
27	7000	65.185
28	6900	72.391
29	6800	55.138
30	6700	57.758

So for example - One group would be Empolyees 1, 6, 27, 28, 29, 30. Their combined salary is 49,700 and their average rating is 69.969
Another group would be Empolyees 6, 7, 10, 11, 14, 15. Their combined salary is 49,900 and their average rating is 75.446.
Employees 1-6 won't work because the salary is to high and 25-30 wouldn't work because the salaries are too low.
So to recap - i want excel to return a list of all possible groups of 6 whose salaries are between 49,000-50,000 and then calculate the average rating of the group.

Thanks in advance for any help.

[JBeaucaire]

Well, this isn't particularly fast, but it will work. Note, there are over 14k combination JUST starting with employee 1. So this will take a while to spit out all 25 levels.

Please Login or Register  to view this content.

[jor2511]

Thank You so much! This works perfect and is easy to change if I need to. Very much appreciated!

[jor2511]

Thank You so much! This works perfect and is easy to change if I need to. Very much appreciated!

[JBeaucaire]

With a little help from some other gurus, we wrote a version of this macro that is SIGNIFICANTLY faster as it does all the work in memory, then writes out all the data at once at the end. try this:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[jor2511]

Wow, that is much faster. Runs in under 30 seconds compared to 3.5 minutes with the other code. I really do appreciate it. Thank You!

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.