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
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