Any way to predict non numeric values?

[t9xlor]

Hello everyone,

Trying to figure out a way to predict next value in the following example.

I have a weighted (non random) six sided dice that has colors instead of numbers. After 10 tosses of the dice, I have collected the following information.

A=Tosses, B=Colors
Toss1, Red
Toss2, Yellow
Toss3, Green
Toss4, Red
Toss5, Blue
Toss6, Black
Toss7, Red
Toss8, White
Toss9, Red
Toss10, White
Toss11, ???

Obviously with this small sample it would be difficult to predict, but hopefully after 1000 rolls of the die, there should be an ability to predict next with some level of certainty. Is this possible?

Thanks in advance!

[FDibbins]

Sounds more like s stats problem than an excel problem?

How would you calc this manually?

[MrShorty]

I expect that any computer language (including spreadsheet) approach to this is going to
1) Assign the number values 1 to 6 to the 6 colors. Probably a lookup table in Excel.
2) Perform the statistical analysis using the 6 numbers.
3) Assign the color values to the 6 numbers, using the same lookup table from step 1.

Steps 1 and 3 are trivial, if you are familiar with lookup tables. If not, then I refer you to the vlookup() help file: https://support.office.com/en-us/art...8-93a18ad188a1 The bulk of the statistical work is in step 2. At this point, I am assuming you already understand the statistics and probability formulas that would be involved. If my assumption is incorrect, then help us understand what part of the probability theory is troubling you, and we'll see if we can help.

[t9xlor]

Yes, the lookup portion is not a problem for me. I have tried a host of straightforward mathematics in Excel but cannot seem to find the right function that best suits this example. I tried things like TREND and FORECAST, but the syntax and return values to seem to be a good fit unless I am using them incorrectly. I'm happy do the work and not looking here for a 100% solution, but if someone could get me headed in the right direction it would be appreciated.

Many thanks!

[quekbc]

Obviously with this small sample it would be difficult to predict, but hopefully after 1000 rolls of the die, there should be an ability to predict next with some level of certainty. Is this possible?

Looking at this from a purely statistical/probabilistic standpoint and some basic assumptions - the quick answer is no. Otherwise everyone would be lottery winners and casinos will go bankrupt.

Basic assumptions 1) it's a fair die, 2) each throws are independent

[MrShorty]

TREND() and FORECAST() perform regressions along a linear (y=mx+b) trendline. I'm not sure how you would be using these functions to understand a semi random event like a dice throw. I would have expected something more like the =FREQUENCY() function to count how many times each side of the dice came up. Once you can assign a probability to each die roll, then you can use the RAND() function and your knowledge of these probabilities to predict the next 10 die rolls.

All of that assumes that I am understanding what you are trying to do here.

And, as the previous poster said, each die roll is independent, so you can state probabilities for the next die roll, but you can't really "predict" the next die roll.

[nahidking]

"each die roll is independent" and this is the key point for which not only excel but only any other super software never can predict the next color. otherwise Lottary business will not exist.

[Kyle123]

But lotteries are fair, a weighted die is not.

I don't see how you'd run the simulation without knowing the weighting to be applied, any wouldn't that weighting simply be the probability of the colour arising so there'd be no need to run the simulation? Or have I missed something?

Wouldn't the given chance of the next colour based on your sample be?

Please Login or Register  to view this content.

Though 10 rolls is presumably not a sufficient sample size

[t9xlor]

Maybe I'm not explaining things very well or maybe I am missing something simple. In my weighted die example, there will begin to establish a trend over time. For example, after 1000 rolls, if RED has come up 600 times, WHITE has come up 350 times, and all the other colors combined make up the other 50, I should know the probability of what colors will come up on the next roll in order to make a prediction. I'm making the example crude, but its the best way I know how to explain what I'm trying to model in Excel.

[MrShorty]

For example, after 1000 rolls, if RED has come up 600 times, WHITE has come up 350 times, and all the other colors combined make up the other 50, I should know the probability of what colors will come up on the next roll in order to make a prediction.

If you have the results of multiple rolls of this die, you can use the FREQUENCY() function (or the COUNTIF() function) to tally up the number of times each side of the dice comes up, then easily estimate probability (# times 1 is rolled/total number of rolls) and do that for each of the 6 sides. (help file for FREQUENCY() function https://support.office.com/en-us/art...7-fd9ea898fdb9 ). Is that the central part of your question?