Rand() formula

[Luke]

I have reading posts that include the rand() function. I am no where near a
mathmatician, my efforts have errors at every turn, so I need to ask for your
help.

Is it posible to use rand() in a formula that would respond with a result
output in column B to my inputting new data into column A? If not Rand(),
then what would work?

simple example:
A B
1 6 5 Output - highest probability to be placed in A15
2 5 based on col A's history.
3 2
4 0
5 7
6 3
7 9
8 4
9 1
10 1
11 8
12 2
13 0
14 3

Thank you for your help
Luke

 

[Gary''s Student]

In your example, how did you arrive with the result 5 ??

 

[Bernd P]

Hello Luke,

Just a guess: If you want to have the most often occuring value, enter
into B1:
=MODE(A1:A14)

Or, if you like its "historical" likelihood:
=COUNTIF(A1:A14,MODE(A1:A14))/COUNT(A1:A14)

If that's not what you intend give another detailed example, please.

Regards,
Bernd

 

[Luke]

Gary,
the 5 is just an example. I could never get it to work so I have no clue as
to what the result would really be.
Thanks for your consideration
Luke

 

[Luke]

Bernd,
Thank you, I basically want to predict the outcome. An educated guess so to
speak.
The result I get with the historical likelihood (which seems to be what I am
looking for) is 0.142857142857143.

Is there a way to translate that result to a "0, 1, 2, ..or 9" as an answer?
in other words as in my example, would "5" be the most likely number to be
next or something else like 0, 3, 8, 6 or so on through 9.

I want the result to be of the numbers that I put into A1:A14.

Hope this makes since.
Luke

 

[Luke]

Please see response to "Bernd P" for better understanding of what I am trying
to do. Thank you Gary
Luke

 

[Bernd P]

Hello Luke,

What's wrong with MODE(A1:A14)?

Regards,
Bernd

 

[Shane Devenshire]

Hi,

Predicting the next item in a series depends on what the basis of that
series is. If it is completely random then the next number is random -
history would have no impact on the future.

You could say that the next value is most likely to be the average of the
historic values in that cast
=AVERAGE(A1:A14)
To return whole number you would round this =ROUND(AVERAGE(A1:A14),0)

If you assumed that the next item was most likely to be the most frequently
appearing value (historically) then you would use MODE.

The problem is you have to define how the series develops. In other words
there is no correct answer to your question as stated.

Another example, suppose you assume that there is a linear trend in you data
you could use:

=TREND(A1:A14,,15)

And you could round this.

 

[Luke]

Hello Luke,

What's wrong with MODE(A1:A14)?

Regards,
Bernd

Hi Bernd,

Nothing, however I like the idea of historical likelihood and was just
courious. I need to understand how it is functioning historically so that the
result makes sense.
Thank you for the help

 

[Luke]

Hi Shane,

You guys certainly gave me something to think about. I want to learn more
about what you have shown me and then if I have questions I will repost and
be more specific about what I am trying to accomplish.

Thank you so much for your input.
Luke

 

[Bernd P]

Hello Luke,

A naive and pragmatic historical lookback needs your definition of the
"horizon" you want to take for the lookback.

A horizon of 1 day lookback would just give you the last number in
A14: 3. That's MODE(A14)
A horizon of 5 days: MODE(A10:A14) would result in 1.
The full history of 14 days MODE(A1:A14) would give you 2.

So it might make sense for you to look at the array
MODE(A14)
MODE(A13:A14)
MODE(A12:A14)
....

But I hope that you are not a doctor who will take this scheme to
predict the desease of his next patient

Regards,
Bernd

 

[Jarek Kujawa]

just a thought
0.142857142857143 = 1/7

Bernd,
Thank you, I basically want to predict the outcome. An educated guess so to
speak.
The result I get with the historical likelihood (which seems to be what Iam
looking for) is 0.142857142857143.

Is there a way to translate that result to a "0, 1, 2, ..or 9" as an answer?
in other words as in my example, would "5" be the most likely number to be
next or something else like 0, 3, 8, 6 or so on through 9.  

I want the result to be of the numbers that I put into A1:A14.

Hope this makes since.
Luke









- Poka¿ cytowany tekst -

 

[Bernd P]

Hello Jarek,

No, its 2/14

The MODE 2 is appearing twice in this given sequence of 14 numbers.
2/14 = .142.... = about 14% likelihood.

Regards,
Bernd

 

[Jarek Kujawa]

ooops, sorry
did not think of that...
;-)

 

[Luke]

LOL Bernd!

Not hardly a doctor.. I am just poking around at the pick three lottery and
although I know I would never get close to any serious wins I like to see how
things play out given the past history.

Probably should have mentioned that but I didn't want the nay sayers to
intervene. It clouds things up.