How many combinations??

[Andrew_UK]

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]

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.