Frank
I did try to explain correctly as possible.
Here is an extract from the original sheet.
Yes I did read the info that you gave, but I'm no mathematician thus I'm still dead in the water.
https://www.mediafire.com/file/di0byuzes9zs8jw/645_Lottery.xlsx/file
BlouBul
I did try to explain correctly as possible.
Really?
I asked you at least 5 direct questions. You did not answer even one of them.
Let me remind you of three of them.
Do you even know WHY "It must be changed to the BINOMDIST formula in Excel"?
What do you know about the function, what made this jump into your head?
You therefore need to be very clear what you are trying to achieve, what information you are going to feed in and what you are wanting to know from this information.
Until then no-one can help you.
Despite all these comments and questions, not one single answer ???
You don't kow what you want the sheet for, what you hope to achieve when its working ?? If
YOU don't know,I'm sure I don't.
Despite your reluctance to tell me what you are up to, I have done some work to find out what the author might have been up to. It appears to be a table to calculate the probability of a number (any number) in a 645 lottery to appear an exact number of times (X) after a specified number of draws (d). Those words One Two three four are just labels. So there are columns of probabilities for each of the set number of elapsed draws 5,10,15,20,30 and the rows correspond to the values of X =0,1,2,3,4.
He has used a custom function
which according to you is using the Binomial distribution to achieve this.
It does look like he got out of his depth and abandoned ship leaving it unfinished.
I have replaced his function with the BINOMDIST function which uses four parameters X,d,6/45,FALSE.
So the probability of exactly 2 appearances of any number (in 645 lotto) after 15 draws is BINOMDIST(2,15,6/45,FALSE) = 0.2095.
Note that the third parameter I'm using is 6/45 = 0.13333. That is the probability of any specified ball of the six numbers drawn appearing in a single draw. I notice that the author mentioned 'position' in his labelling, which I have ruled out since the BINOMDIST function doesn't care about position. In any case if the numbers were sorted after being drawn then the probabilities of being in a certain position are a whole different ball game.
The question arises "What use are these figures" ? Beats me unless there is another part of the spreadsheet that does something clever with the figures. I'm wondering if the author was using a sledgehammer to crack a nut, trying to work out how many times a number SHOULD be out by any given number of draws d.
Take a look at the results for 30 draws:-
30
None 0.0137
ONE 0.0631
TWO 0.1407
THREE 0.2020
FOUR 0.2098
FIVE 0.1678
SIX 0.1076
SEVEN 0.0567
EIGHT 0.0251
NINE 0.0094
TEN 0.0030
ELEVEN 0.0009
TWELVE 0.0002
You will notice that the highest probability 0.2098 occurs after 4 draws. So the table tells us that after 30 draws any ball would statistically be most likely have been drawn 4 times.
But you don't need the BINOMDIST function to get this information. Its a very easy calculation. its (number of draws ..30) x 6/45=4. Simple. (((Shrug))).
On Sheet 2 is bigger more sophisticated table expanded to over 200 draws and 46 appearances, the lotto matrix is not fixed at 6/45
it can be changed. The maximum probability in each column is used to find the expected number of appearances of a ball at that number of draws - read off row 7.
The problem with using BINOMDIST is that the parameters have to be compatible or you get a #NUM! error. For example you cant ask for the probability that a number is drawn 20 times in 10 draws, thats a silly impossible question. That is the reason my formulas are more complicated, they have to filter out the #NUM! errors which occur on the rows and columns of sheet 2 when X is greater than d and also filter out any non zero values which are infinitesmally small.
https://www.mediafire.com/file/q74clde8gg7jrgl/645_Lottery.xlsx/file
That's what I think is going on anyway.
