I'm doing some data analysis within excel and i'm looking for a formula / systematic approach for solving an equation programmatically. When I looked at the analysis, it's similar to some lotto combinatorial math.
Q. Is there a formula that could calculate all possible combinations (sets) of say 6 numbers that would equal a total (sum)? Using a lotto example, find all combinations of 6 numbers (from 49) that equal X.
Example, the two sets below both equal 100. However there are many more combinations of possible numbers within the range from 1-49 that also would equal 100.
Set 1 10 14 15 17 18 26 = 100 Set 2 4 10 14 15 26 31 = 100 Set 3... Set 4... List all other possible combinations that equal sum of 100.
Thinking formula would be need 3 variables: 1. Size of set (combination) e.g., 6 2. Upper bound Number of Range to choose from e.g., 49 3. Sum total solving for e.g. 100
"Formula" ideally would take - any number for size of set (6, 8 etc.) - variable for number of upper bounds of range (49, 59 etc) - Sum total solving for (100, 500 etc.) Output: (excel?) would display all possible combinations (sets) that would equal Sum Total
Assumption is same as Lotto where number cannot be re-used. i.e., only one 5, only one 22 or only one 49.
Would appreciate greatly any thoughts or comments on how to achieve this. I'm currently trying to facilitate via MS Excel.
Thanks all!