On the attached example I have 7 acts performing in a talent competition and besides each act the probability that the judge will give the act a yes vote or no vote.
Beneath this table I want to workout the probability that zero acts from the seven will get a yes vote, one act will get a yes vote etc. through to all seven getting a yes vote.
Zero and Seven are easy enough as they can just be a straight forward 7 fold accumulator but how can I formulate 1 act to 6 acts?
Thanks.
Hi adam2308,
I'm sure this is one of those binomial theorum problems but here is another way to solve it.
Next to each Act put a Rand() formula. Use this to calculate a Yes or No in the column to the right, based on the criteria for each judge. Then do a formula that counts the number of Yes after the Random Nunbers. Put this process in a VBA loop and do it about a thousand times. If you count correctly you will have the number of zero Yes, One Yes, Two Yes, etc.
This is the way we can use computers to solve these problems.
See the attached for the loops and VBA Code.
Last edited by MarvinP; 05-30-2011 at 08:44 PM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thanks for this Marvin. I'm sure you are right about it being a binomial distibution formula but I too am unsure of how to use the binomial function in this situation.
I understand what has been done here by using the random formula against each act and I guess this would work well. It is also an interesting to simulate the number of yes votes.
Can I just check the VBA code is correct though? I have had a look at the code and although I can't say I completely understand it, I have managed to change the number of loops to just one rather than 1,000 to test it and it doesn't seem to work how I understand it should.
The way I understand it is when you press the button the worksheet does a calculate and 1 is added to the count in the table B10:C17 corresponding to the count in cell G8.
Marvin's Monte Carlo approach is great.
For cases this small, you can enumerate the probabilities as attached. The numbers agree well with Marvin's, and his would be even closer if you increased the number of trial.
There may well be a way to solve this in closed form, but I don't know the method.
Entia non sunt multiplicanda sine necessitate
Hey Adam,
I'd bet if you multiplied this out
the coefficients of the values would give you the answers you want.
Please Login or Register to view this content.
A = all yes, B = 1 yes, C = 2 yes, D = 3 yes, E = 4 yes etcPlease Login or Register to view this content.
See if that works.
Sorry for the delay in coming back this thread but I got stuck on another project and only just got back round to looking at this again.
The Monte Carlo method of MarvinP's is very interesting and I am confident it would give us accurate probabilities as requested. However, I amended the code in the button so that it just loops once and found that it did not always work as expected. If you look at the attached example for instance where I have run one simulation and the count in G8 is 4 but the 1 got added the 'Two Votes' count.
This therefore leaves me concerned the total counts for 0,1,2 etc are not accurate after running 1,000 tests.
Does anyone understand why this is happening and if they can see where the error is?
Thanks,
Adam.
Excel recalculates after the result is written.
ahhh yes I see!! I run the code using step into and I can see what you mean.
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks