Terrygordon
asked on
How to select all combinations that will achieve a minimum score
Hi
I have a qualification where students must select 6 study units from a list of 18. Each study unit has a different credit value (some are low credit and some are high credit) and the students must choose any 6 units to achieve a minimum of 372 credits.
Is there any way to do this on an Excel spreadsheet, so that 'ALL' possible combinations of 6 units are shown, using formulas or VBA? I have managed it, using formulas, when there are only 2 unit combinations, with a target of 121 credits, but can't quite wrap my head around 'any 6 from 18' with a target of 372.
I have attached a spreadsheet with the unit numbers, credit values, target and an example of OK and not OK combinations.
Any help would be much appreciated.
Regards
Terry
Unit-Permutations-Problem.xlsx
I have a qualification where students must select 6 study units from a list of 18. Each study unit has a different credit value (some are low credit and some are high credit) and the students must choose any 6 units to achieve a minimum of 372 credits.
Is there any way to do this on an Excel spreadsheet, so that 'ALL' possible combinations of 6 units are shown, using formulas or VBA? I have managed it, using formulas, when there are only 2 unit combinations, with a target of 121 credits, but can't quite wrap my head around 'any 6 from 18' with a target of 372.
I have attached a spreadsheet with the unit numbers, credit values, target and an example of OK and not OK combinations.
Any help would be much appreciated.
Regards
Terry
Unit-Permutations-Problem.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Rob
Really appreciate your solution. Given the number of permutations, I agree that it probably will be easier to use the drop-down lists (or an on/off) button just to check that the combination meets the criteria.
Still, good to know how to identify all possible options, for future reference.
Thanks for your help.
Regards
Terry
Really appreciate your solution. Given the number of permutations, I agree that it probably will be easier to use the drop-down lists (or an on/off) button just to check that the combination meets the criteria.
Still, good to know how to identify all possible options, for future reference.
Thanks for your help.
Regards
Terry
Happy to help, thanks for the feedback.
Didn't mention how I got the 18,564 result. As mentioned on that website there are a couple of functions for this kind of thing
PERMUT(Total number of items, number of items chosen)
COMBIN(Total number of items, number of items chosen)
They both have the same parameters, total number and number of items chosen. The difference between them is basically whether there can be repetitions. The PERMUT function allows for repetitions of a set of selected numbers eg 1 2 3 4 5 6 and 6 5 4 3 2 1 would both be counted as they are different permutations, whereas the COMBIN function allows only one occurrence of a set, the two examples above would be counted as one set as they both contain the same numbers.
Mathematically they are calculated, with your example of 6 items from 18:
PERMUT = 18 x 17 x 16 x 15 x 14 x 13 = 13,366,080
COMBIN = (18 x 17 x 16 x 15 x 14 x 13)/(6 x 5 x 4 x 3 x 2 x 1) = 18,564
I carried on with generating the table only because I was intrigued to find if it was possible; if you were to ask me how the code works, I wouldn't be able to answer. I understand array formulas and a reasonable amount of VBA but that code is beyond me. If you need to know I would suggest commenting on the source page to get further clarification.
Regards
Rob
Didn't mention how I got the 18,564 result. As mentioned on that website there are a couple of functions for this kind of thing
PERMUT(Total number of items, number of items chosen)
COMBIN(Total number of items, number of items chosen)
They both have the same parameters, total number and number of items chosen. The difference between them is basically whether there can be repetitions. The PERMUT function allows for repetitions of a set of selected numbers eg 1 2 3 4 5 6 and 6 5 4 3 2 1 would both be counted as they are different permutations, whereas the COMBIN function allows only one occurrence of a set, the two examples above would be counted as one set as they both contain the same numbers.
Mathematically they are calculated, with your example of 6 items from 18:
PERMUT = 18 x 17 x 16 x 15 x 14 x 13 = 13,366,080
COMBIN = (18 x 17 x 16 x 15 x 14 x 13)/(6 x 5 x 4 x 3 x 2 x 1) = 18,564
I carried on with generating the table only because I was intrigued to find if it was possible; if you were to ask me how the code works, I wouldn't be able to answer. I understand array formulas and a reasonable amount of VBA but that code is beyond me. If you need to know I would suggest commenting on the source page to get further clarification.
Regards
Rob
The web site below explains a bit more about the difference between PERMUTATIONS and COMBINATIONS if you didn't already know.
https://medium.com/i-math/combinations-permutations-fa7ac680f0ac
https://medium.com/i-math/combinations-permutations-fa7ac680f0ac
ASKER
Thanks Rob. The maths is the easy bit - it’s coding it and producing named outputs that’s the difficult part. 😊
As Rob stated, there are 18,564 possible combinations. Of those, only 981 meet your criteria. Attached is a spreadsheet showing those combinations. You can filter column N to show just the ones that equal or total more than 372.
Paul
6-18NumCombo.xlsx
Paul
6-18NumCombo.xlsx
Do you really want to create a list of all 18k items???
Or would it be better to just have a way of entering a combination of 6 items and getting the score?
See attached. Range K3 to K8 has Data Validation based on the list of 18 items so user can select items correctly. If duplicate items are chosen the duplicates go orange with red text and error message below list. Range L3 to L8 then uses VLOOKUP to populate the credit value for each unit and sums them in L9 with a variance below in L10. The SUM will only show when there is no error message in K9. The outstanding Credits required will only show when total credits is less than required otherwise just shows as zero.
Unit-Permutations-Problem.xlsx