Autocreating probability distribution

[Zerex71]

Greetings,

I have been wondering about this for some time. I'm doing a lot of
work with probability and statistical distributions these days, and
many of the classes or types of distributions have different parameters
which characterize them. For example, standard distributions tend to
have a (mu, sigma) which defines the familiar bell curve. My question
is, is there a way to enter those two numbers (or function
characteristics of any number) and have Excel autogenerate the the
row/column data from which a bell curve can be automatically drawn on a
subsequent chart? By this I mean, I'd like to not have to manually
have a column which uses a function to calculate the values at that
point and copy the function to every cell yielding a new F(x) -- I want
Excel to be able to take my numbers and blam! fill in the columns for
me. Also, I'd like to be able to add a checkbox for the user to select
either discrete or continuous, and have the chart draw itself
accordingly (i.e. either discrete, integral vertical lines forming a
curve shape, or an actual curve like what you'd find on a "non-pointed"
scatter chart). Thanks.

Mike

 

[Jerry W. Lewis]

That functionality is not built into Excel. However, you could write a
subroutine in VBA to do it.

Note that some probability distributions are not uniquely characterized by
their mean and standard deviation. Also, some distributions do not have a
mean or standard deviation. For example Student's t distribution requres
df>=2 for the mean to exist and df>=3 for the standard deviation to exist.

Jerry

 

[Zerex71]

Hi Jerry,

I didn't mean to blow you off, I've just been busy with other things.
But I did come back to this question a bit and had some more comments.

I understand that there are different distribution types, and my boss
provided me with a handy spreadsheet that he put together a while ago
to help him understand the different distributions. It's actually done
well enough to be useful to me (i.e. up to my Excel standards).
Anyway, my original question was just about whether or not Excel had
any capabilities to draw a standard distribution given things like
mean, sigma, skewness, kurtosis, etc.

My specific example is this: I have a golf spreadsheet wherein I've
kept track of all my scores in my league. I calculate all the
distribution parameters above. I was wondering if there was a way to
autogenerate the discrete distribution rather than manually plotting
it. So far I have not found a way, and as you say, it sounds like that
is not possible.

Also, I plotted something else to complete this golf spreadsheet and
had a question about it. I know there are CDFs and PDFs, and one is
basically related to other by way of an integral. I've been trying to
refresh my old prob/stat coursework as well as read about the topics on
mathworld.wolfram.com, but the jargon is too academic to answer my
simple question.

I now have two interesting graphs:
- Count of the number of times I've scored a particular score, as a
function of score
- Probability that I will score a given score, as a function of score

Hopefully that's not too confusing. Basically, the data for the first
one is something like, I scored 65 three times this year, so f(65) = 3.
The data for the second one is something like, f(65) = 0.20. Which is
the CDF and which is the PDF? To further complicate matters, I did
what I am calling a Monte Carlo simulation where I played 13 (no
particular reason for this number) "rounds" by inputting a random
number for each hole and bounding that by the minimum and maximum
allowable scores per hole in my league (minimum is usually just par; I
never score it and would never get less than par, and maximum is double
par, by my league's rules). Then, after having tabulated all that
data, I can get the same data as described above but for simulated
play, and plot that coincident with the data from actual play. So far
there is no correlation but that's a topic for another day.

Mike

 

[Jerry W. Lewis]

As I said before, this functionality is not built into Excel. You can do it
manually, cf.
http://www.tushar-mehta.com/excel/charts/normal_distribution/
or you can automate that process by writing a VBA macro.

In the case of the Normal distribution, the NORMDIST function takes its mean
and standard deviation as arguments. The various distributions take
"natural" parameters that may be different from their mean and and standard
deviation (assuming that they exist). However, you can calculate the mean
and standard deviation (assuming that they exist) as functions of these
natural parameters. For example with the chi-square distribution with df
degrees of freedom, the mean is df and the standard deviation is 2*df. With
the binomial distribution with n trials each with probability of success p,
then the mean is n*p and standard deviation is SQRT(n*p*(1-p)).

Excel probability functions are not consistent as to whether they calculate
the cumulative distribution (cdf) or non-cumulative (pdf [continuous] or pmf
[discrete]), both, or neither (upper tail for Chi-square, both tails for
Student's t). A consistent and much more accurate library of (VBA) functions
can be downloaded from
http://members.aol.com/iandjmsmith/examples.xls

Jerry