Permutations in Excel

The odd of 6/6 =
COMBIN(6,6)*(1/10)^6*(9/10)^0
= 1,000,000 to 1

5 in 6
=COMBIN(6,5)*(1/10)56*(9/10)^1
=18,519 to 1

see attached file

Cheers
Dave

See as attached ... first row is the number of inputs and the column A through ... is the number of selections

Chris

file attached this time ....

so N from  6 is

= COMBIN(6,N) * (1/10)^n*(9/10)^(6-n)

in total the 7 probabilities (0,1,2,3,4,5,6 correct) add up to 100%
Cheers
Dave

Chris,

It's correct that there are 210 ways to pick 6 bottles from 10 - order unimportant

But in this case it's how many correct guesses at random from those 6 bottles, with a 1/10 chance that any particular random guess is correct, 9/10 that is is wrong

Cheers

Dave

Agreed that i'm wrong ... mind stuck on winning the lottery!

Chris

<slightly off topic - Hawkeye, I thought your name was familiar   http://www.experts-exchange.com/Q_20810477.html  :) >

Brett, I'd completely forgotten about that one - one bored night playing with numbers :)


I had got the figure of 210 (6 out of 10, any order), there's a handy online calculator for that:
http://www.mathsisfun.com/combinatorics/combinations-permutations-calculator.html
And I can recreate that in excel as suggested: combin(10,6) or more fundamentally: (10*9*8*7*6*5)/(6*5*4*3*2*1) - one number per cell, =product(A1:A6)/product(B1:B6)
which works well for any variety of "Choose x from y, any order"

However that says you have 6 choices and you get 6 right.
What I can't get (I don't think anyone's given it yet) is if you have 6 choices (with 10 to choose from) and only have to get 5 right. (3 variables)
I tried just deleting the 5, and the 6 and the 7 from the top row, but that gave a result that said you have a 1 in 1 chance of being right if you have to get 3 out of your 6 correct. This must be wrong, as with 6 choices and 10 wines, you could feasibly choose 4 wrong ones and only 2 correct ones. You couldn't get worse than 2 right, so the odds of getting 2 out of your 6 right is 1 in 1.

That's with the right order,
What I'm looking for is "any order" so 6/6 will be 1 in 210, and 2/6 will be 1 in 1, and 3, 4 or 5/6 will be somewhere between those two

We have crossed wires on the intent of your question - or better put, I misunderstood

There are 210 ways of picking 6 bottles out of 10. So far so good

My initial "solution" was aimed at getting every single one of these 6 bottles individually correct.  From your post above I now understand that you aren't worried about getting the individual bottles right, its the chances of having  mathing countries somewhere within your selected 6 bottles. as you say there are only 5 possible outcome - I'll rerun the calcs on this basis

The chances (out of 210) of getting n right is

=COMBIN(6,n)*(combin(10-6,n)

so for n =6 that will give you 1

for n=5 that will give you 24.....then 90 for 4, 80 for 3 and 15 for 2, giving a total of 210

It's not possible to get 1 or 0 right with 6 out of 10

regards, barry

Obviously those numbers I gave are the exact numbers of 6,5,4,3,or 2 correct so to get the number with at least 4 right, for instance, you'd add the exact numbers for 4 5 and 6, i.e.

90+24+1

so you have 115/210 = 54.8% chance of at least 4 correct

regards, barry

Sorry...I still managed to get the generic equation wrong, the number of instannces (out of 210) of n correct are

=COMBIN(6,n-6)*combin(10-6,n-6)

so, for instance, if n = 5

=COMBIN(6,1)*combin(10-6,1)=24

regards, barry

nicely done Barry :)

Although I'm still curious on the actual question ..... Wouldn't the aim of the quiz be to get the actual bottles right bottle by bottle, rather than somewhere in the selected collection (ie the Australian wine would hate to be called UK wine even if they both were in the selected 6) . Intuitively that doesn't sound skill based :)

I suppose it's more of a lottery based question rather than a wine-based one?

I hope you can work that out from my posts, hawkeye, I would attach a worksheet but I'm unable to do that right now. IF you want I can attach a generic version which would do the calculations for any x bottles from y

regards, barry

Good question, Dave, I'll ask the boss!
It's for the wine show in London next week, so anyone who's close can come and see the probability in action and have a drink too!


Barry, that sounds good, but first of all the formula is slightly back to front. In combin(x,y), x must be bigger than y. But if you put 5 into your formula above (ID ..354), you get a negative in the first part, and a #num! in the second.
I think it should read: =COMBIN(6,6-n)*combin(10-6,6-n), which gives the results you quote above.

This sounds good, where n=2, it gives odds of 1 in 15 (as you've said above, and as per attached file). But the odds of choosing 2 correct with 6 to choose, and 10 choices, is 100%. So either the formula is flawed or I've misinterpreted that 15 figure.... what do you think?

To get those figures in your sheet then use this formula in F12

=SUM(E$12:E12)

and copy down to F16

and then in G12 copied down to G16

=F12/SUM(E$12:E$16)

format as %

regards, barry

That's ideal. But does it make sense?
What we're doing is multiplying the chance of getting the ones you choose right by the chance of getting the ones you don't choose right? but you don't choose the ones you don't choose, so why do we include probability for something that is not happening?

That may be a philosophical question, and you don't have to answer it for the points, just curious.

Charlie

Hello Charlie

This is the way I looked at it:
 
Consider the situation where you get 5 right out of 6, how many possible combinations are there for that?
 
If you get 5 right then you only have 1 correct country missing, so there are 6 possibilities, each one of the correct 6 missing
 
now for each of those 6 possibilities there must be a replacement (wrong) country. There are 4 of those so for each of the 6 missing correct countries there are 4 possible wrong countries which could replace it, therefore 6*4 = 24
 
Now for 4 right the same logic applies&..there are 2 correct countries missing out of 6 so thats 15 possibilities&and there must be 2 replacement wrong countries, any 2 out of 4 = 6, 6*15=90
 
Mathematically of course you get the number for the correct countries missing with
 
=COMBIN(6,6-n)
 
and the number for the combinations of replacements with
 
=COMBIN(10-6,6-n)
 
and the result is those 2 multiplied together.
 
Perhaps there are other ways&&..but I dont know of any
 
regards, barry