Most probable digit return ?

[serge]

Hi,
I wanted to know if it would be possible to create a formula or code that would return in cell M3 the most probable digit 1,2,3, which start in column M6:M317 ? Thank you.

1

1

1

1

1

1

1

3

2

3

3

3

1

2

3

1

2

3

1

1

3

2

3

1

3

3

2

1

1

3

3

1

1

3

2

1

2

1

2

2

2

1

2

3

2

1

1

3

1

1

3

2

3

2

3

3

2

2

2

1

2

2

1

3

1

3

3

2

3

2

1

3

1

2

3

3

3

2

2

2

2

2

3

1

3

1

1

3

2

2

3

2

3

1

3

3

1

2

3

1

2

2

1

3

2

1

1

1

1

2

3

1

1

3

1

1

2

1

2

2

1

2

3

2

2

2

3

3

1

3

2

3

3

1

1

3

1

2

1

3

1

2

3

1

2

2

2

2

3

2

1

3

1

2

2

3

2

1

1

3

2

3

3

3

3

1

3

2

1

3

2

3

2

1

3

1

3

3

1

1

3

3

2

1

1

1

1

3

1

1

1

1

3

2

2

2

3

1

1

3

2

2

1

2

3

1

3

2

3

2

2

3

1

1

3

3

1

2

3

3

1

1

3

2

3

1

2

3

1

3

2

2

3

2

2

2

3

1

3

3

2

3

2

1

2

3

2

2

1

2

3

2

2

1

3

3

3

2

3

3

1

1

2

3

1

3

1

1

2

3

2

2

2

3

3

2

3

3

1

1

1

1

3

1

3

3

3

1

3

1

1

2

3

2

2

1

3

2

2

1

3

3

2

2

2

2

3

3

2

3

1

3

 

[hagia_sofia]

when I use your formula and I pasted it in my next column, the data in the previous columns change Why ?

This is because RANDBETWEEN is a volatile function that recalculates with (almost) every change in your workbook. If you would like to stop it from happening you could e.g. paste the results as values. It really depends on what is the goal though...

 

[serge]

Ok, the goal is to estimate the most accurate possible digit to be next without having the workbook to change each time a new data is added, my understanding is that the next value should be it and not change because the column data DOESN'T CHANGE, so the return digit should be fixed with that formula or code, I hope I make myself clear in this explanation ?

 

[Eric W]

What do the 1, 2, and 3 values represent? I don't believe it's possible to come up with a method to predict the most likely next value in the sequence without having some idea of the underlying structure. Are they win/loss/tie values for a sports team? Rainfall accumulation in a given location at sequential dates? Stock returns? Number of cars seen passing a given location in a given interval? I'd model every one of those differently.

Or maybe they are just random values, and you're trying to analyze the random number generator. If so, do you know the distribution? Uniform, normal, binomial, or something more exotic? Do the values depend on previous values, or are they generated independently? If they do depend on previous values, is it all the previous values, or just the previous n values?

Even if we could come up with a formula, it would take hundreds, if not thousands, of observations to feel even a little comfortable with it. There are statistical methods to estimate the effectiveness of such a formula. I doubt they'd ever give you over a 50% rating. The MODE suggestion is as good a method as any. It essentially assumes that the value that occurs most often is most probable. By adjusting the range, you could base the probabilities based on the most recent observations. But if the numbers are generated with a uniform distribution, then you could just say that the most probable next value is 1. Or 2, or 3, since they'd be equally probable.

Anyway, if you could provide more information, we might be able to come up with some ideas, but just long rows of values with no context won't help much.

 

[Rick Rothstein]

I agree with @Eric W, if would be helpful to know how this sequence is being generated. Without knowing that, a guess might be to put COUNTIF(M6:M317,1), COUNTIF(M6:M317,1), COUNTIF(M6:M317,1) in three different cells and look to see which has the maximum value and use the number that generated it as your sought after value. For the data you posted, the 3 formulas return 101, 100, 111 so 3 would be the guessed out answer.

 

[ebea]

That kind of question, has been up before, in other forums. Next Number in Sequence without a solution!

But now, that we have both ChatGPT and MS Copilot, I did "ask" both of these AI models. And interesting, they both came up with the number 2 as the most likely, without presenting HOW they came to that result. And ChatGPT has a limit of the numbers in the sentence which can be set in, which Copilot not seems to have.

 

[ebea]

As a little follow up, to #15.

I made a new test with ChatGPT with the whole row of your numbers (which I could'n do in the first test on ChatGPT), where it was reduced (and gave number 2 as most likely next number).

And here what ChatGPT did answer to the full row of numbers.

---------------------------------------------------------------------------------------
To predict the next most probable number in the given sequence, let's first analyze the frequency of the numbers in the series.

The numbers in the sequence are primarily 1, 2, and 3. Here’s a quick breakdown of their frequencies in the sequence:

• 1 appears most frequently.
• 2 also appears frequently but less so than 1.
• 3 is the least frequent of the three but still appears quite often.

Given the pattern and considering the sequence’s structure, we can analyze the last few numbers in the sequence to look for trends.

The last few numbers in the sequence are:

• 3, 1, 2, 3, 2, 3, 2, 2, 1, 3

In this small recent subset, the number 3 appears quite a few times, which suggests that 3 might be a strong candidate for the next number in the sequence.

Conclusion:​

Based on the most recent numbers and the frequency of 3, the next most probable number is likely 3.
------------------------------------------------------------


As you see, the predict now changes to be 3, instead of 2, with more numbers to test with.

So in reality, I can't see how it ever could be possible to have a method to predict a number, out of an amont of 1, 2, 3, no matter how many numbers put' in.

 

[serge]

Hi and thank you all for your responses so those data are the results of lottery draws, yes they are just random values, and I'm trying to analyze random numbers, that I coded with 1,2,3 t this way it would have more repetitions patterns than if I use 1,2,3,4,5,6,7,8,9 which would look more wild and harder to select, the system that I created is flawless and work for any lottery in the world as long as the number of balls don't go over 70, if I'm able to guess the 8 numbers right, let say in the last row in this example 11,11,13,13,12,23,23,32 then it will return the 5 numbers plus the mega ball in 1 line together in more or less 1 to 5 sets out of 300 millions, this is a hobby where I get very close sometime, but haven't get all of them yet, so I'm trying to find out if a formula , code or piece of software could help me get closer, the picture I posted is just 29 draws, but on my workbook I have 313 draws and of course can add much more to get more insight for analyzing it, in this 313 draws the total time those numbers came out ordered from min to max is : 13 = 20 times, 23 = 21 times, 31 = 25, 33 = 31, 22 = 32, 11 = 37, 12 = 44, 21 = 48, and the 32 = 55 times so they don't all come out equally and by using this MODE formula I guess the 32 will be selected each time isn't it since it came out the most ? Here in this screen shoot I put in a CF to show that after to digitr 1 ( if it's not a repetition ) the digits 3 come out more often than the 2 I'm just trying and if it's not possible then forget it, if someone come out with something positive I don't mine to share tickets. Thank you guys again for all your insights.I extended a bit the table after the orange row.

 

[serge]

HAPPY NEW YEAR TO EVERYONE,

I tried my best to explain but don't get reply, thank you all for your input, really appreciate it, I know it's not an easy task but that's where I'm now and will maybe never be solve but I'm trying.