Formula to Predict daily Odd-Even Patterns

If you are looking for a way to look at past random data, then predict future random data, it's not possible. If it were possible then I would be in a casino instead of answering Excel questions.

If that is not what you want to do, then you need to give much more of an explanation of what algorithm you are using to generate the data, and what what algorithm you think will predict future data.

Thanks for the response.

Lol it's for the daily lottery, I'm trying to predict the Odd even pattern of each number position.
I already have excel sheets that is giving me the numbers, if I know the odd even patterns correct.

I tried this formula on another sheet, it was giving me say 60% correct predictions.

Formula:

Please Login or Register  to view this content.

But the problem was it did not automatically predict as I entered the previous days results.

I'm not sure I am following what you are trying to do.

This formula =IF(MOD(COUNTIF(B$2:B$2000,"E"),2)=0,"O","E") appears to:
1) count the number of "E" entries in column B. Column B is empty, so this is 0. For column C, this returns 728. D=736, E=742, F=751, G=781
2) The MOD(...) part of the function appears to be testing if this count is even, so this appears to be equivalent to ISEVEN(COUNTIF(...))
3) If the count is even, return "O". Otherwise, if the count is odd, return "E".

Am I understanding the current formula correctly? When you say "it does the job, but is not accurate," what do you mean? What do you want the formula to do differently?

How are you measuring the accuracy of your predictions?

What algorithm do you want to use for making predictions? Currently, it appears that you are counting the number of Es and Os and somehow using that to choose Es and Os. You say that this is randomly generated (from an allegedly fair random generator), and my quick look does not see any obvious patterns or other reason to think that the generator is not fair. What patterns do you think exist and how are you identifying them?

Trying to stay at anyday, use your current COUNTIF (in row 1) to evaluate the history vs that day results.
Not too many 100% found...

Administrative Note:

Unfortunately, it has come to our attention you have violated Rule #8 of our Forum RULES:

Don't private message, visitor message or email Excel (or Access, Word, etc.) questions to moderators or other members.

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Breaking this rule is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offense could lead to a permanent ban, so do take this caution to heart.

I don't know what algorithm to use for making predictions, that's why I'm looking for Help.

Now we are back to 6stringjazzer's original point/question. If this is a good, fair random number generator, there will be no patterns or biases to be detected and exploited.

We are good at programming known algorithms into Excel. We are not always very good at developing the algorithms from scratch. In cases like this, we are often skeptical that a prediction algorithm exists, which further dampens our desire to spend time chasing this particular wild goose.

Do you have reason to believe that there are biases and patterns to be found in what is supposedly a fair and unbiased random number generator?

Originally Posted by Wit24

The numbers are generated mathematically and randomly

If this is true it is mathematically impossible to predict future numbers with accuracy better than random chance. The whole field of statistics is based on this idea.

If you are not familiar with our rules, please take the time to review them. There aren't many, and they are all important.

I hear what you are saying. I'm not chasing a wild goose.

I think one of your tasks right now is convincing anyone here who might be able to help that it isn't a wild goose chase.

I don't really have the statistical background needed to do this sort of thing, but, if I thought an alleged random number generator was not random, I would start by researching methods that statisticians might use to test a random number generator. My level of statistics only knows basic chi square goodness of fit testing (something like this: https://www.statology.org/chi-square...s-of-fit-test/ ), so, when all you have is a hammer, everything looks like a nail, I would start there.

One test might be to see if the random number generator seems biased towards odd or even. =COUNTIFS(data,"O") and =COUNTIFS(data,"E") return 3621 and 3739. =CHITEST({3621,3739},{3680,3680}) returns 0.17, which would usually fail to reject the null hypothesis that odd and even are equally likely.

I could (like you started to do on sheet2) assign "binary" numbers to each 5 digit pattern. I let O=0 bit and E=1 bit, so that each 5 digit combination yields a unique number between 0 and 31. Then counting those patterns and performing another chi squared test, I get a result of 0.04 from CHITEST(), which suggests a 95% confidence that one of the patterns is under/over represented, but not at a 99% confidence level. If I can convince myself that the bias is real, I could use the observed distribution as a basis for choosing the next 5 digit set. But I doubt I would dramatically improve my prediction scheme over random choice.

From there, I could get endlessly lost down the rabbit hole of attempting to detect patterns.

If you will tell us how you want to detect patterns and implement a prediction algorithm, then we should be able to help you program that algorithm into the spreadsheet. I'm not sure any of us has the statistical expertise needed to chase this goose without further guidance from you.

There was one user on this forum: greenfox74. But sadly he past away last year. He was programming everything in Excel and he got whole YouTube channel where he explained everything.

I guess my best bet now is to continue down the rabbit hole with the assistance of ChatGBT.

Thanks everyone for the help.

Originally Posted by bebo021999

Trying to stay at anyday, use your current COUNTIF (in row 1) to evaluate the history vs that day results.
Not too many 100% found...

@bebo021999

The workbook that you helped me with. I noticed something with this formula =IF(MOD(COUNTIF(B$2:B1475;"E");2)=0;"O";"E") there are only a few 100% results. But when I use =IF(MOD(COUNTIF(B$2:B1475;"O");2)=0;"O";"E") I noticed that the 0% results are also 100% with this formula.

I combined this two formulas to one: =IF(OR(MOD(COUNTIF(B$2:B1475;"E");2)=0;MOD(COUNTIF(B$2:B1475;"O");2)=0);"O";"E") in order to increase the 100% results. But I'm not sure if there is another formula that will work better?

Thanks in advance

I'm sorry if I could not leave this alone, but, as skeptical as I am that there is any way to predict the next draw from past draws, I find these kind of things interesting terms of statistical analysis.

I started with bebo021999's file from post #9. I then added columns for your extension from post #20. Then I added columns that chooses randomly. I changed the calculation from % correct to # correct, then analyzed each algorithm (columns AF to AJ).

Probability theory was always my weakest math subject, but I think the theoretical percentages in column AK are correct. These represent the percentage you should get correct in a 5 number draw if you are just guessing. I included the just guessing algorithm (columns V:AA) for comparison.

What this should give you is a way to know if any proposed algorithm is truly an improvement. These three algorithms all match the theoretical "random chance" probabilities pretty closely. As you and your AI bots and anyone else you recruit comes up with new algorithms/formulas, this will allow you test and see if they are truly skewing the distribution towards 4 and 5 correct, or simply matching the random chance algorithm.

Excel has a few filtering tools. I expect that Power Query/Get and Transform has tools for filtering, but I am not at all familiar with Power Query. Help file: https://support.microsoft.com/en-us/...7-a628f9b89353

There is also the FILTER() function if you have access to it: https://support.microsoft.com/en-us/...c-4877ad80c759

The only filter tool I am familiar with is the autofilter tool. Autofilter can filter by multiple criteria, but I don't think it will like the logic you want here -- "filter records where either M, T, or AA is 5."

In this case, I would add a helper column that does the "is either M, T, or AA equal to 5," then use Autofilter on that single column. Maybe enter something like =OR(M3=5,T3=5,AA3=5) into G3. Then filter the list using column G for records where G is TRUE.