Combinations equaling a sum: a new twist

Questions like this have been asked, but not with these parameters AFAIK. In a set of 6 numbers ranging from 1 to 10, with possible repetitions. does any combination of from 2 to 4 numbers add up to 15? This is a classic cribbage problem, with these differences from the usual question about combinations and target:

1. There are 10,000 hands dealt to get the answer for.
2. The hands of 6 numbers are in rows, not columns.
3. Only 4 of the 6 numbers may be used, but any 4.
4. I don't need to know the combinations totaling 15, only whether there is at least one. (Printing out one or indicating none would be sufficient.)

Excel's Solver won't work, because my material (the cribbage hands) is in many rows, which means there's no room for the extra data entries Solver requires (and it seems to work only on columns anyhow).

I'd be grateful for suggestions. I can install a VBA script but can't create one. I use a Mac, so Windows add-ons won't work.

The attachment shows the results of a presumed IF calculation, Y for satisfying the condition, N for not. Note that in the last row, 10 + 2 + 1 + 1 + 1 = 15, but that's too many numbers, the maximum being 4.

Numerist,

I must have misunderstood the question, because if the Ace counts as "1" then an Ace, Two, Three and Nine total 15? If the Court cards counts as 1o, then any one of them and a Five are fifteen? And even without the Court cards, Two, Four and Nine total fifteen.

Ochimus

Any combination of 2 to 4 cards each valued 1 to 10 add up to 15? There's always brute force.

Put the following 3 tables somewhere and name them as in the underlined labels.

T2Cards
1 2
1 3
1 4
1 5
1 6
2 3
2 4
2 5
2 6
3 4
3 5
3 6
4 5
4 6
5 6

T3Cards
1 2 3
1 2 4
1 2 5
1 2 6
1 3 4
1 3 5
1 3 6
1 4 5
1 4 6
1 5 6
2 3 4
2 3 5
2 3 6
2 4 5
2 4 6
2 5 6
3 4 5
3 4 6
3 5 6
4 5 6

T4Cards
1 2 3 4
1 2 3 5
1 2 3 6
1 2 4 5
1 2 4 6
1 3 4 5
1 3 4 6
1 4 5 6
2 3 4 5
2 3 4 6
2 4 5 6
3 4 5 6
3 4 5 6

Then try these formulas.

G1:

Formula:

Please Login or Register  to view this content.

Fill G1 down as far as needed.

Another approach. More named ranges.

T2C
*10*5*
*9*6*
*8*7*

T3C
*10*4*1*
*10*3*2*
*9*5*1*
*9*4*2*
*9*3*3*
*8*6*1*
*8*5*2*
*8*4*3*
*7*7*1*
*7*6*2*
*7*5*3*
*7*4*4*
*6*6*3*
*6*5*4*
*5*5*5*

T4C
*10*3*1*1*
*10*2*2*1*
*9*4*1*1*
*9*3*2*1*
*9*2*2*2*
*8*5*1*1*
*8*4*2*1*
*8*3*3*1*
*8*3*2*2*
*7*6*1*1*
*7*5*2*1*
*7*4*3*1*
*7*4*2*2*
*7*3*3*2*
*6*6*2*1*
*6*5*3*1*
*6*5*2*2*
*6*4*3*2*
*6*3*3*3*
*5*5*4*1*
*5*5*3*2*
*5*4*4*2*
*5*4*3*3*
*4*4*4*3*

Then

G1:

Formula:

Please Login or Register  to view this content.

Fill down as far as needed.

This is slightly less brute force than the previous approach. Actually, you could combine T2C, T3C and T4C into a single range, maybe named T_C, then use the much shorter

G1:

Formula:

Please Login or Register  to view this content.

Thanks for those. I'll have a look. (I've fixed a couple of minor things in your lists. They don't affect the main idea.)

If I could generate one of those lists for larger numbers by an algorithm, I'd have a chance of getting everything I need. (The larger deck-and-hand combinations I'm interested in require much higher combinatorial results.)

Kindly reread my original post. Solver will probably not work. VBA is possible but I can't write the code myself. Currently hrlngrv's ideas are better.

Numerist,

If you are willing to consider VBA, the attached identifies all two, three and four card combination rows.

DATABASE sheet has five hundred "hands" of six random cards.
COMBINATIONS sheet lists the forty combinations that can make fifteen, allowing for repetitions.

When you click the button, Two-card "matches" are flagged up in Col H, Three in Col I and Four in Col J
Summary on COMBINATION sheet shows the % of matches

Please Login or Register  to view this content.

Sure someone will provide a more elegant version

Ochimus

to Ochimus: Thank you for that elaborate work. Unfortunately the spreadsheet-and-macro crashes my computer. Although I may try it again, I've recently found other ways to get the results I need.

Using hrlngrv's 1st method and my simple formulas to generate the combinations, I now have all the data I need. My calculations go as far as a 204-card deck, including zeroes and wild cards, with 12 cards dealt to each of two players, who reduce their hands to 8. About half the decks are dozenal, meaning I convert all values to "base twelve" and use a dozenal deck ranging from 50_z to 146_z cards (decimal 60 to 198).