Could someone help me with an excel formula to run a test 1000 times where there are only two possibilities of the event to occur out of 40 outcomes. Is it possible in excel?
For example: I have 40 numbered stones in a bunch from 1 to 40. How many times I randomly pick stone no.1 or 2 when I try this 1000 times?
Is it possible to execute this in excel and get a detailed answer in %?
Here's one way:
Put this formula in A1 and copy down to A1000, it will act as the random event of choosing a stone: =RANDBETWEEN(1,40)
In another cell, put this formula to get the count of 1's and 2's:
=SUM(COUNTIF(A1:A1000,{1,2}))
To get the percent, well, I'm sure you can figure that out.
Every time you click F9 the random numbers in column A will recalculate.
Of course, Excel doesn't generate truly random numbers.
This particular statistical problem has been well studied by mathematicians, so you don't have to simulate but can use a well known result. Or, if you must simulate (say, for homework), this can provide a way to test it.
When you have an event E that can happen 1/20=0.05 of the time, and you want to know a probability that the event E happens K (K=0,1,2,3,...,999,1000) times in N=1000 tests, the answer to that is the Binomial Distribution B(n=1000,p=0.05).
In Excel, this Binomial Distribution is available with BINOMDIST().
BINOMDIST(49, 1000, 0.05, FALSE)
gives the probability of seeing K=49 events out of N=1000 tests when the event has prob p=0.05.
FALSE is for one particular outcome of the 1000 tests, i.e. K=49.
TRUE would give the cumulative probability of less than or equal, i.e. summing up the probabilities for K=0,1,2,3,...,49 events.
In cell A1 enter =RANDBETWEEN(1,40) then copy it down to B1000
In cell C1 enter =IF(OR(AND(A4=1,B4=2),AND(A4=2,B4=1)),1,0)
in cell D1 enter =SUM(C:C)/1000
Pressing F9 will recalculate
