
October 17th, 2011, 07:45 PM


Executive Member


Join Date: Apr 2006
Posts: 5,141


excel, data & standard deviation Canceler?
Canceler you there? lol ......... help, lol......
errhh ok, i got some data.
daily earnings
and i know what the expected value for daily earnings is
so is there a function in excel that i can use to estimate the standard deviation?
i was gonna use STDEV but there's no where in that function to enter the known expected value.
any help?
edit: so maybe there shouldn't be anyway in there to enter ev, i guess, right?
like ok if i use STDEV, is that figure produced, one standard deviation?
can i come up with one of those bell curve graph's with my data?
Last edited by sagefr0g; October 17th, 2011 at 11:30 PM.

October 18th, 2011, 12:14 PM


Executive Member


Join Date: Apr 2005
Location: Minnesota
Posts: 1,625


Sorry, was out late last night playing poker.
Okay, this will need a little thought and research. Meanwhile, regardless of Mr. Fr0g’s personalization of this thread toward me, anyone who knows the answer should feel free to chime in.

October 18th, 2011, 01:49 PM


Moderator


Join Date: Mar 2006
Location: Los Angeles, CA
Posts: 4,748


Quote:
Originally Posted by sagefr0g
errhh ok, i got some data.
daily earnings
and i know what the expected value for daily earnings is
so is there a function in excel that i can use to estimate the standard deviation?

It sounds like you have a daily average (EV) but not the daily standard deviation so you are trying to estimate it from a series of daily results. Is that right?
You would need a pretty large sample size (population) to get anything reliable, but if that's what you're tying to do I can help you. Ideally you would know the SD of your playing strategy and could use that to find your daily SD. Finding the SD from sample data can be dangerous, especially since most casino games have pretty wild swings that may skew your estimations one way or the other.
Quote:
Originally Posted by sagefr0g
i was gonna use STDEV but there's no where in that function to enter the known expected value.

The reason that the Excel function does not let you input the EV is because it uses the data samples to find it. If your daily average (EV) is very different than the average of your daily results then that is a good indication that things are going to be off.
Quote:
Originally Posted by sagefr0g
like ok if i use STDEV, is that figure produced, one standard deviation?

Yes, that would be one standard deviation.
Sonny

October 18th, 2011, 02:32 PM


Executive Member


Join Date: Apr 2006
Posts: 5,141


Quote:
Originally Posted by Sonny
It sounds like you have a daily average (EV) but not the daily standard deviation so you are trying to estimate it from a series of daily results. Is that right?

right. i could do it by plays but some of my data is kind of screwed up.
Quote:
You would need a pretty large sample size (population) to get anything reliable, but if that's what you're tying to do I can help you. Ideally you would know the SD of your playing strategy and could use that to find your daily SD. Finding the SD from sample data can be dangerous, especially since most casino games have pretty wild swings that may skew your estimations one way or the other.

unfortunately, i have no idea the SD per the playing strategy.
but yes, wild swings aren't unexpected, so ok that is helpful to know that the data can skew the estimations.
erhh, dunno if this helps that conundrum, but i do know fairly accurately worst and best case scenario numbers for an average day, sorta thing.
they aren't actual data numbers but they at least define limits for best and worst swings. maybe those could be worked into the data?
Quote:
The reason that the Excel function does not let you input the EV is because it uses the data samples to find it. If your daily average (EV) is very different than the average of your daily results then that is a good indication that things are going to be off.

well, so far the average of daily results is fairly close to daily average ev, as is overall results close to ev.
so but i guess that could be skewed by wild swings and a relatively modest amount of data, sorta thing.
so ok, thank you, helps clarify things quite a bit.
Quote:
Yes, that would be one standard deviation.
Sonny

ahhh ok, thank you, and ok i realize that could be quite off since it's coming from a small data pool.
so maybe if i could get excel to turn the data into a bell curve or attempt to do so, then if it didn't look much like a bell curve, then definitely not enough data, or something off, maybe?

October 18th, 2011, 03:09 PM


Executive Member


Join Date: Feb 2007
Posts: 2,267


dust in the wind
Quote:
Originally Posted by sagefr0g
ahhh ok, thank you, and ok i realize that could be quite off since it's coming from a small data pool.
so maybe if i could get excel to turn the data into a bell curve or attempt to do so, then if it didn't look much like a bell curve, then definitely not enough data, or something off, maybe?

If you know its not enough data then a graph won't have much meaning. If your houly EV is fairly tight you can guess the SD is not high? I would think you have a very weak idea of SD from your play.
With real tight earnings the bell would be tall and thin? To the earnings side.
If you don't know your SD hope your betting conservatively.
I dont know
1/4 Kelly comes to mind
Don't want the misses mad

October 18th, 2011, 04:16 PM


Executive Member


Join Date: Apr 2006
Posts: 5,141


Quote:
Originally Posted by blackjack avenger
If you know its not enough data then a graph won't have much meaning. If your houly EV is fairly tight you can guess the SD is not high? I would think you have a very weak idea of SD from your play.
With real tight earnings the bell would be tall and thin? To the earnings side.
............

so to plot a bell curve, do you just use the raw data?
to where, what? i guess sort the negative values from highest value to lowest and sort the positive values from highest value to lowest, then marry them up on the graph chart so to speak? errh but then i guess you'd need to then make the negative values positive?:
errhh sorta like i did in the image below, where the numbers in red were negative but i changed them to positive.... sorta thing?confused:
edit: erhh excel gives me a SD of $265.27 for this data........
ev is $65.83
Last edited by sagefr0g; October 18th, 2011 at 04:30 PM.

October 18th, 2011, 04:42 PM


Executive Member


Join Date: Feb 2007
Posts: 2,267


point graph?
Isn't there a graph where you just plot points and then connect the dots, that may look interesting?
How many sessions have you played?
Think of the swings in 100 coin flips, actual results are about meaningless.
In BJ we need about a 250 million and larger sample size? At least for sims?
Last edited by blackjack avenger; October 18th, 2011 at 04:53 PM.

October 18th, 2011, 05:17 PM


Executive Member


Join Date: Apr 2006
Posts: 5,141


Quote:
Originally Posted by blackjack avenger
Isn't there a graph where you just plot points and then connect the dots, that may look interesting?
How many sessions have you played?
Think of the swings in 100 coin flips, actual results are about meaningless.
In BJ we need about a 250 million and larger sample size? At least for sims?

looks like a witch's hat for Halloween, lol
errhh 94 plays in 31 days.......

October 18th, 2011, 05:32 PM


Executive Member


Join Date: Apr 2006
Posts: 5,141


not enough data =====>>>>
so to make those graphs reflect an ev of $65.83, i'd have to have one heck of a lot more data, no?
edit: errrhh but shouldn't the peak of the bell curve coincide with the EV?
how can it ever do that when it's topping out at nearly $800, at this point.
errhh am i not supposed to use the raw data for the chart, or what?
edit: hmmphh, must be doing something wrong here, as far as representing the data in the chart, no?
Last edited by sagefr0g; October 18th, 2011 at 05:51 PM.

October 18th, 2011, 05:53 PM


Executive Member


Join Date: Feb 2007
Posts: 2,267


point graph
A point graph you plot the days in order of occurrence and then connect the dots?

Thread Tools 

Display Modes 
Linear Mode

Posting Rules

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off



All times are GMT 6. The time now is 01:41 AM.
