Generating Scatter Plots

[iacas]

I'm hoping that someone I tag at the bottom of this thread - or someone else - can help me out with something.

I'd like a way to generate a scatter plot on a cartesian coordinate system that obeys a bell curve but lets me set the standard deviation (the same would apply in both X and Y directions).

If it generates 100 "random" (fitting the standard deviation I set) points, that'd be perfect. I should be able to press a button to get a different set of 100 "random" dots.

The alternative would simply be a random number generator that obeys the bell curve and can kick out 100 (or 200, if I have to pair them) numbers that follow the standard deviation I set.

Can Excel or something else do this?

@jamo @boogielicious @Lihu @saevel25 @Golfingdad

[iacas]

http://www.random.org/gaussian-distributions/ ?

[iacas]

I hate when I research something, then when I write it up, I use a word or something that triggers a slightly different search, and then I find the answer. :P

[saevel25]

Yea I don't think Excel can handle that with out some fancy excel work.

The only thing I could think of would be, set two cells in excel to be your standard deviation, and mean.

Then you can do something like

1 / ($A$1*SQRT(2*PI())) * EXP (- ((RANDBETWEEN(0,???) - $A$2)^2)/(2*$A$1^2)))

Were ??? Is the max random number you want. Not sure if that works out for ya. Just drag and click. Every time you edit something on the worksheet it should update the values, RANDBETWEEN is active like that. Not sure if that is correct or not, or if that is what you want.

[mdl]

Excel has the inverse CDF for a few common distributions, including the normal.  With the inverse CDF, you can just plug in a uniform random number (ie RAND()), and get a random number distributed by the PDF of the distribution you used the inverse CDF for.

So, for a random number distributed normally with mean 0 and SD 1, you can just use NORMINV(RAND(); 0; 1)

One of the other built in inverse CDFs in Excel is the log-normal.  That might be useful Erik if you want to plot non-symmetric landing spot scatters.  Most players definitely have a skewed distribution of distance (we all chunk one 2/3 of the way there but do we ever airmail the green by 33% of the distance we meant to hit it).  And many players have a skewed miss distribution.  As I've been trying to learn to draw the ball my left/right misses have gotten more equal in percentage terms, but it used to be my shot either hit the green/fairway or went right, with a long tail off to the right.  You could try using the lognormal (LOGINV) or gamma (GAMMAINV) to get skewed miss distributions (though those are both only defined on positive numbers, so you'd have to, say, subtract the mean to get misses left and right of 0).

[saevel25]

Excel has the inverse CDF for a few common distributions, including the normal.  With the inverse CDF, you can just plug in a uniform random number (ie RAND()), and get a random number distributed by the PDF of the distribution you used the inverse CDF for.

So, for a random number distributed normally with mean 0 and SD 1, you can just use NORMINV(RAND(); 0; 1)

One of the other built in inverse CDFs in Excel is the log-normal.  That might be useful Erik if you want to plot non-symmetric landing spot scatters.  Most players definitely have a skewed distribution of distance (we all chunk one 2/3 of the way there but do we ever airmail the green by 33% of the distance we meant to hit it).  And many players have a skewed miss distribution.  As I've been trying to learn to draw the ball my left/right misses have gotten more equal in percentage terms, but it used to be my shot either hit the green/fairway or went right, with a long tail off to the right.  You could try using the lognormal (LOGINV) or gamma (GAMMAINV) to get skewed miss distributions (though those are both only defined on positive numbers, so you'd have to, say, subtract the mean to get misses left and right of 0).

COOL!!! Learn something new.

[Pave]

There is another factor to consider, for a right-hander, long left, short right, I'm sure you get what I mean.

[iacas]

So, for a random number distributed normally with mean 0 and SD 1, you can just use NORMINV(RAND(); 0; 1)

One of the other built in inverse CDFs in Excel is the log-normal.  That might be useful Erik if you want to plot non-symmetric landing spot scatters.  Most players definitely have a skewed distribution of distance (we all chunk one 2/3 of the way there but do we ever airmail the green by 33% of the distance we meant to hit it).  And many players have a skewed miss distribution.  As I've been trying to learn to draw the ball my left/right misses have gotten more equal in percentage terms, but it used to be my shot either hit the green/fairway or went right, with a long tail off to the right.  You could try using the lognormal (LOGINV) or gamma (GAMMAINV) to get skewed miss distributions (though those are both only defined on positive numbers, so you'd have to, say, subtract the mean to get misses left and right of 0).

Like this?

I added a 0.2 multiplier on the right column: if you miss left, the shot will tend to be a little longer, and if you miss right, a bit shorter.

The actual standard deviation is kind of irrelevant since I can just scale the image up or down to fit anyway.

One of the other built in inverse CDFs in Excel is the log-normal.  That might be useful Erik if you want to plot non-symmetric landing spot scatters.  Most players definitely have a skewed distribution of distance (we all chunk one 2/3 of the way there but do we ever airmail the green by 33% of the distance we meant to hit it).  And many players have a skewed miss distribution.  As I've been trying to learn to draw the ball my left/right misses have gotten more equal in percentage terms, but it used to be my shot either hit the green/fairway or went right, with a long tail off to the right.  You could try using the lognormal (LOGINV) or gamma (GAMMAINV) to get skewed miss distributions (though those are both only defined on positive numbers, so you'd have to, say, subtract the mean to get misses left and right of 0).

Feel free to modify my Excel file and re-post, but… so far as I can tell we can just drag the scatter plot around a bit to get a fairly good representation. I don't want to make it too complex - chunking a shot now and then just won't factor, because you can't really "plan" for those anyway. So these are shots hit reasonably well.

[mdl]

There is another factor to consider, for a right-hander, long left, short right, I'm sure you get what I mean.

Yeah, but this gets more complicated because you can't generate independent random numbers for the X and Y dimensions.  You need to create numbers from a 2-dimensional distribution where there is correlation between the dimensions.  This is relatively easy to do in a stats language (e.g. R), but is a huge pain in the ass in excel.

[iacas]

Yeah, but this gets more complicated because you can't generate independent random numbers for the X and Y dimensions.  You need to create numbers from a 2-dimensional distribution where there is correlation between the dimensions.  This is relatively easy to do in a stats language (e.g. R), but is a huge pain in the ass in excel.

I dunno. I feel like I did it reasonably well. :)

I take the shot's value on X (left/right) and use it to create a value for Y by setting the mean to -0.2X.

[Pave]

Spot on!

Really like where this is going.

[saevel25]

I dunno. I feel like I did it reasonably well. :)

I take the shot's value on X (left/right) and use it to create a value for Y by setting the mean to -0.2X.

So why the 0.2 for the vertical?

[iacas]

So why the 0.2 for the vertical?

Because shots hit left (pulls) go farther. Shots hit right go shorter. It's actually -0.2 to account for this.

So a shot "10" left will have its mean set to "2" long.

[boogielicious]

Looks like the way I would have done it in Excel.  I used to use MathCad and software from the MathWorks for work.  It had these functions.  But that was a dozen years ago.  I haven't touched in since maybe 2002.

[mdl]

I dunno. I feel like I did it reasonably well. :)

I take the shot's value on X (left/right) and use it to create a value for Y by setting the mean to -0.2X.

Great solution for getting a reasonable looking scatter plot.  Was thinking it would be a pain in the ass to exactly generate a bivariate distribution with exactly the covariance and means you want, especially for anything non-normal, but this is obviously the right solution for getting a good looking scatter plot!  Reduction of SD for Y variable was a nice touch too, giving it more of a look of a bivariate correlated distribution.

As I noted above, one thing you could play with is skewed distributions to give, say, some of the way short or big push-slice shots common among most everyone except low-mid single digit or better players.  Attached is an edited excel sheet using two gamma distributions to get this effect.  It's more of a hack than with the normals, but you can play around with it to get it to look like you want.  The two parameters to the Gamma variable in column A are alpha and beta.  Those are the second and third args to the GAMMAINV fxn.  The mean of a gamma distribution is alpha/beta, and the standard deviation is sqrt(alpha / beta^2).  So you can play around with alpha and beta and the very ad hoc stuff I put in for alpha and beta in column B to shift how it looks if you feel like it.

https://dl.dropboxusercontent.com/u/101944576/200PointPlot_Gamma.xlsx

How do I add a non-pic, non-video attachment?

[iacas]

How do I add a non-pic, non-video attachment?

The paper clip icon.

Thanks. I'll download that and try it out. I don't know if we care too much about the weird outliers. People tend not to factor those in or believe that they hit those as often as they do anyway, so if we can make our case with a normal distribution (skewed slightly that long left/short right is in there too), then we should be okay.

[mdl]

The paper clip icon.

Thanks. I'll download that and try it out. I don't know if we care too much about the weird outliers. People tend not to factor those in or believe that they hit those as often as they do anyway, so if we can make our case with a normal distribution (skewed slightly that long left/short right is in there too), then we should be okay.

Yeah, fair enough.  If you're just trying to make the aim where you scatter plot scatters around the center of the green point, then you probably don't need to worry about the skewed distribution.  Could make an interesting newer players' add-on point where if your right-left distribution in particular is skewed with a fat tail off to the right (lots of fades and slices, a few pulls, no hooks), where you want to aim the heaviest part of the scatter plot changes depending on what's off to the right.  Something like if missing at all to the right is super penal, maybe OB is just to the right of the green, then maybe you actually want your median shot left center or even left edge of the green, accepting that a good number of decent shots will be off the green left in exchange for many fewer OB shots.

Ah.  I never actually noticed the "More" option off the right of the comment box.  I swear I'm on top of shit...

[boogielicious]

Thanks @mdl for the great info. My only issue is when reading your posts, I imagine the bear avatar speaking.