Flexible Bernoulli Table in Excel

[PAB]

Hi Everyone,

For Anyone Interested, this is How to Setup a Flexible Bernoulli Table in Excel Using the BINOMDIST ( Binomial Distribution ) Function.

Function Definitions ( Parameters ) :-
number_s = the Number of Successes ( a Number Coming Out in a Draw ) to be Obtained in the trials.
trials = the Number of Independent trials ( Draws ).
probability_s = the Probability of Success on Each trial.
cumulative = a Logical Value that Determines the Form of the Function.
If cumulative is TRUE, then BINOMDIST Returns the cumulative Distribution Function, which is the Probability that there are at Most number_s Successes.
If cumulative is FALSE, then BINOMDIST Returns the Probability Mass Function, which is the Probability that there are number_s Successes.

Function Syntax :-
BINOMDIST(number_s,trials,probability_s,cumulative)

Setup :-
In Cell "B3" Enter the Total Number of Balls that are Drawn ( 6 Numbers in a 649 Lotto ).
In Cell "B4" Enter the Total Number of Balls Drawn from ( 49 Numbers in a 649 Lotto ).
In Cell "B5" Enter FALSE Or TRUE ( See the Difference in the Definitions Above ).
In Cells "C7:M7" Enter the Values 0 ( Zero ) to 10.
In Cells "B8:B108" Enter the Values 0 ( Zero ) to 100 ( for 100 Draws ).

In Cell "C8" Enter the Formula :-
=IF(ISERROR(BINOMDIST(C$7,$B8,COMBIN($B$4-1,$B$3-1)/COMBIN($B$4,$B$3),$B$5)*100)," ",BINOMDIST(C$7,$B8,COMBIN($B$4-1,$B$3-1)/COMBIN($B$4,$B$3),$B$5)*100)
Drag this Formula Across to Cell "M8" and Down to Cell "M108".
You may want to Format the Cells "C8:M108" to TWO Decimal Places.

That is it.
You can Update the Table for Other Lotto's by Either Changing the Total Number of Balls that are Drawn ( Cell "B3" ), OR Changing the Total Number of Balls Drawn from ( Cell "B4" ) OR BOTH.
You can Also Change the "cumulative" Parameter by Amending the FALSE to TRUE ( Cell "B5" ).

I don't know if this will be of Use to Anyone, But the Table Analysis is Certainly Interesting.

Have Fun.
All the Best.
PAB

 

[PAB]

Hi Everyone,

I should Probably Add that the Values in Cells "C7:M7" are the Number of Times of a Ball Appearing.
So for Example, the Percentage Probability of a Ball Appearing 3 Times in 14 Draws ( with the "cumulative" Parameter Set to FALSE ) in a 649 Lotto will be Found in Cell "F22". The Answer Being 15.88%.

Have Fun.
All the Best.
PAB

 

[tomtom]

Hey PAB,

Haven’t tried your tables since from some reason never liked and never really understood logic of some excel formulas, but must say you’re such a nice guy and hope at least some of those bookies there will pay you a lot of $$$ soon..

All the Best

 

[tomtom]

Hey PAB,

Haven’t tried your tables since from some reason never liked and never really understood logic of some excel formulas, but must say you’re such a nice guy and hope at least some of those bookies there will pay you a lot of $$$ soon..

All the Best

I meant here since never really understood logic of some of excel formulas in global, never liked using them, so it has nothing with the formulas you used of course....

 

[PAB]

Hi tomtom,

No Problem, Thanks for your Nice Comments.
I think it is Important on our Journey to Winning the Big One to Enjoy it and Make it Fun, as well as Trying to Pick up some Wins Occasionally to Alleviate the Overall Expenditure. Sharing Strategies, whether Good Or Bad, gives us a Better Understanding of the Many Filters that we can Apply to the Lotto.
I got the Inspiration to Produce the Bernoulli Table from a Post by Grandmaster, who Himself, was Inspired by LottoArchitect ( Thanks to BOTH of You ). It has Benefited me Because it has Allowed me to Find Out and Understand the Excel Function BINOMDIST. Whether it will Help me to Win the Lotto is Another Matter, it is Something Else to Consider and Possibly Incorporate.
I have Learnt a Lot from this Great Board and its Knowledgeable Members, and I am Always Looking to give Something Back, that is why I Hope the Table will be of Use to Someone. The More Information we have got the Better the Chance ( Hopefully ) we have of Cracking it.

All the Best.
PAB