Normal Distribution (2003/SP2)

Topic

New Reply

I apologize if this is in the wrong forum, but it is the most appropriate that I could find.
In creating the chart as instructed here, I am unable to see any value in Series 2 (Histogam-Bin). Can someone provide insight?

TIA

Reply | Quote

Replies

Hi Don

I am useless at such things and although I attempted it I could not get past the fat that there was text in column B and I got an error because of it, when i removed the text however I did get information in E,F,G and H but some it was #N/A

Does this page help you any better? http://www.tushar-mehta.com/excel/charts/n…l_distribution/%5B/url%5D

They seem to suggest that there is more than one way to skin this cat

Cheers

Steve

Reply | Quote

Hi Steve
Thanks for the link. It put things into perspective for me.

Reply | Quote

I think they made a mistake. The “frequency” they calculate do not match since the data has only 8 points and the random sets has 2000. Therefore the frequency of the 2 ranges will have much different maximum.

To fix this I added the percentages of the frequency to be plotted. I also changed from an XY to a line for the curve and a histogram for the data.

Steve

Reply | Quote

Hi Steve
Thanks for the feedback, but I think the purpose of the KB article was to show the difference between the two distribution curves (8 point data and the 2000 point Normal curve).

Reply | Quote

If it was, I don’t see the point of the random curve that is created.

I thought the point was to create a comparison of the curve from the 8 points to a normal distribution. If done this way, they both must use the same number of points or use proportions not actual numbers.

Steve

Reply | Quote

Hi Steve

We seem to be on different pages.

The Tools > Data Analysis > Histogram activity collects all the entries from the identified range and drops them into a bin representative of those we created in C2:C8. Now all graphs can be seen on the same axis.

As you will see in the attached file, I have strayed from the instructions by plotting series 3 instead of series 2 against the secondary axis. So we can now see the comparison of the to curves.

I am still at a loss to see any value in series 2.

Reply | Quote

As for the series 2, I also see no value to it. [And re-reading your original, this seemed to be your real question and I apologize for missing it…] According to their directions this seems to be the range that should be on secondary axis…

[The selection of an XY scatter is also a poor choice since one of the X values is text which means that the chart becomes line-chart. The numbers in E are only plotted in the chart as the “series 2”, as a “X” it plots the numbers 1-8. If one actually called it a line chart, the Xs would be the values in E]

I was looking more for trying to understand your “goal” and modifying their technique.

As you have it series 1 and 3, are not showing them on the same axis. You have the 2 on different axes. This seems to me to defeat the purpose of plotting one dataset and the normal distribution expected for that dataset. The distributions need to be normalized to have a total of “1” under the curve use the actual data (which should total 8 for hte 8 points) and the expected (the normal distribution *8).

Typically one plots a histogram of the real data and the expected “normal curve” based on the average and standard deviation of the data. They should be plotted on the same scale

Alternatively one can plot the actual points (instead of the distribution) versus the expected probability distribution and see if the points are linear. If they are not you have points which deviate from “normal”.

Steve

Reply | Quote

Thanks for your time Steve.

I think that MS did a less than stellar job of explaining what was happening, but through our discussion I believe that I now have a reasonable handle on it.

Reply | Quote

I agree about the MS article. I think it could use some work. I am glad I could help, though I think you had a pretty good handle on it before you posted the question. It seems to me that the “less than stellar job” just lead to a little confusion…

Steve

Reply | Quote