# How many combinations??



## Eclectic Lady (May 22, 2015)

Hello,

This may very well not be the place to post this question but I'm a writer not a math wiz and quite simply don't know how to figure this out.

I'm writing a newspaper article on hoarding and would like to determine how many different Christmas trees I can decorate using seven variables (different balls, bobbles and other embellishments) with a maximum grouping of two variables per tree/year.

For the sake of the calculation let's pretend there are equal amounts of each of the following items: Red satin balls; gold satin balls; red glitter icicles; gold glitter icicles; red mirrored snowflakes; gold mirrored snowflakes and red silk poinsettia bracts.

To avoid clutter—difficult for most hoarders I know—I'm limiting the combinations used each year to two.  Sooo, one year I use red glitter icicles with gold mirrored snowflakes. . .the next year I use gold satin balls and red poinsettia bracts.

How many years can I decorate the same artificial tree without repeating the combinations?  Is it even within the span​ of human life expectancy?

Thanks!!
Sheree


----------



## sheetspread (May 22, 2015)

try shg's chinese food menu:

https://app.box.com/shared/ubd28tissi

and divide the number of combinations by 2 or however many per year.


----------



## shg (May 22, 2015)

=combin(7, 2) returns 21.


----------



## Eclectic Lady (May 23, 2015)

Abac ad ae af ag
bc bd be bf bg
cd ce cf cg
de df dg
ef eg
fg


----------



## Eclectic Lady (May 23, 2015)

Thanks Sheetspread and Shg...

I looked at the Chinese Food Menu and was VERY intrigued but don't have the time now to sort it out (but will return someday). . .

Shg:  Your formula "=combin(7,2) returns 21" was also interesting but I'm a bit rusty on which numbers/factors to plug in where. . .

BUT I did my own little diagram to see if I could come up with 21 and voila! It worked:

ab ac ad ae af ag
    bc bd be bf bg
         cd ce cf cg
             de df dg
                  ef eg
                      fg

21!!

Is that the answer then?  Seven different decorations....when used two at a time. . .will yield 21 different combinations?  

Wow....if that's the answer then there's hope. . .I might live long enough to use all 21 combinations....

THANKS
Sheree


----------



## Eclectic Lady (May 23, 2015)

The alignment of my diagram above went wacky when I hit Post, thus explaining why it doesn't make sense now.  In reality my columns were aligned properly...


----------



## shg (May 23, 2015)

sheetspread said:


> try shg's chinese food menu:
> 
> https://app.box.com/shared/ubd28tissi



There's a later version at https://app.box.com/s/47b28f19d794b25511be


----------



## sheetspread (May 23, 2015)

Eclectic Lady said:


> Is that the answer then?  Seven different decorations....when used two at a time. . .will yield 21 different combinations?





			
				shg said:
			
		

> There's a later version at https://app.box.com/s/47b28f19d794b25511be



Using the menu program (yes I should have found the later version, thanks for posting it, I was wondering why the older one didn't have the macro I remembered):


Excel 2010BCDEFGHIJK2Cum:4971,13Cnt:771,24Header:121,35Inputs:111,46221,57331,68441,79552,210662,311772,4122,5132,614#2,7151111,13,3162121,23,4173131,33,5184141,43,6195151,53,7206161,64,4217171,74,5228211,24,6239222,24,72410232,35,52511242,45,62612252,55,72713262,66,62814272,76,72915311,37,73016322,33117333,33218343,43319353,53420363,63521373,73622411,43723422,43824433,43925444,44026454,54127464,64228474,74329511,54430522,54531533,54632544,54733555,54834565,64935575,75036611,65137622,65238633,65339644,65440655,65541666,65642676,75743711,75844722,75945733,76046744,76147755,76248766,76349777,7using FormulasCell FormulasRangeFormulaC2=PRODUCT(relS:$D$3)C3=COUNTA(Symbols)C15=INDEX(Symbols, MOD(INT((Line - 1) * Cnt / Cum), Cnt) + 1)B15=ROW()-ROW(B$14)D15=INDEX(Symbols, MOD(INT((Line - 1) * Cnt / Cum), Cnt) + 1)F15=MIN(C15:D15)&","&MAX(C15:D15)Named RangesNameRefers ToCells'using Formulas'!Cnt='using Formulas'!B$3'using Formulas'!Cum='using Formulas'!B$2'using Formulas'!Line='using Formulas'!$B2'using Formulas'!relS='using Formulas'!B3'using Formulas'!Symbols=INDEX('using Formulas'!B$4:B$14, 2):INDEX('using Formulas'!B$4:B$14, ROWS('using Formulas'!B$4:B$14) - 1)

(Paste to column K, remove duplicates and identical pairs)


----------



## Micron (Jun 6, 2015)

This all looks like a lot of work when you could just use the COMBIN function in Excel, right? It gives the answer 21.


----------



## sheetspread (Jun 6, 2015)

Yes but if you need the combinations instead of just the count.........


----------



## Andrew_UK (Jun 16, 2015)

Hi Eclectic Lady,

As a visual representation I would suggest saying if you have N variables and are looking to see how many different pairings you can make then you have N distinct first choices and N-1 second choices (since you DON'T want to choose the same one twice and there's N-1 others). Now that gives us N*(N-1)... you could draw a chart if that would help you and scribble out the squares that don't apply.

Now finally consider that Tinsel + Fairy Lights is exactly the same as Fairy Lights + Tinsel, so everything in our table is exactly duplicated. We can say this with certainty because we start with N different options and remove that one from the pool at that point. So we need to divide N*(N-1) by 2

N*(N-1)/2

This is actually the formula for triangular numbers and is considered to be a very elegant mathematical formula which crops up in lots of different scenarios.

For more complicated calculations I suggest using Combin(Number of different options, Number of selections) as others have suggested.


----------



## MARK858 (Jun 16, 2015)

Andrew_UK said:


> For more complicated calculations I suggest using Combin(Number of different options, Number of selections) as others have suggested.



Hi Andrew_UK,  IMHO I would still use Combin even for the less complicated calculations because 

a) probably because it is a built in function of Excel Combin calculates a bit faster than N*(N-1)/2 (tested using FastExcel V3), although we are talking fractions of a millisecond (with 100 of each formula on a slow work computer for example the average calculation time on a range was Combin (with a row reference for the No of combinations) = 2.934 milliseconds, N*(N-1)/2 (with a hard number for the No of combinations)  =3.208 milliseconds (the difference has been consistent over multiple runs and size of ranges with Combin just edging it each run)).

b) given a) then I would go for the ease of use of Combin

Just my personal opinion.


----------

