Normal distribution of following range of values?

Topic

New Reply

Please see attached sheet (zipped to reduce size). There is a range of values in C3:C1411 which I have plotted as a ‘scatter’ chart at the bottom. I want to know if the range of values is in a “normal” distribution. If so,how do I plot to illustrate that and how can I determine what percentage fall within various ranges (0-0.1,0.1-0.2, etc.)? Thanks in advance.

Reply | Quote

Replies

See return attachment.

I used the histogram tool from the analysis took pack (add-in under Tools Menu). Then plotted the Bin (x axis) against Frequency (y axis). The Histo tool will graph for you as well.

You can easily see the distinctive bell curve of a mostly normal distribution. How normal is it? Give the Descriptive Statistics tool a try. It will give you the quantitative stuff statisticians crave.

Reply | Quote

Kbelesky’s solution appears to be what I was looking for. Just one more question – how do I calculate the area under the curve to confirm this is a “normal” distribution? (If I remember correctly, we learnt in school that Mean +/- 1StdDev equals 65% of the normal curve, Mean +/- 2SD equals 87% (?) of the curve, etc. –correct me if I am wrong!!! I am only going by my memory!!!)

Reply | Quote

A quick and dirty method is to use the feature of a normal distribution – that mean = mode = median. These functions are all available (mean is AVERAGE()

Sometimes the mode cannot be calculated, but it’s always inspecting your data. This helps that.
Ruth

Reply | Quote

There are several ways to check the normality (Gaussian distribution or bell-shaped distribution) of your data. You can do a statistical test (e.g. Shapiro-Wilk test or Kolmogorov-Smirnov test). Unfortunately, these tests are not available in Excel and you have to use specialized statistical software to perform these tests. Another possibility is to judge the normality either visually by plotting a normal probability plot or QQ-plot or by evaluating the ratio of the interquartile range and the standard deviation. This ratio should be close to 1.34 if the data is normally distributed. The problem here is: what is acceptable and which deviation (either from the visual inspection of the plot or from the value

Reply | Quote

Whew! Thanks, Hans, for that very detailed & meticulous explanation. Unfortunately, much of that went w-a-y over my head; my head is still spinning. I will struggle to implement the tiny portion I understood. Meanwhile, more responses are welcome, especially if they involve only 2-3 mouse clicks!
Note to Moderators: I heartily recommend Hans for some recognition.

Reply | Quote

This morning, when everything was fresh and clear for me, I had a look at your data in the attached file. Your data is NOT normally distributed, but right-skewed (you have an asymmetric distribution with a long tail to the right. So, be careful to draw conclusions from average, standard deviation and the functions Normdist and Norminv. If the data is not normally distributed, these functions might lead you to wrong conclusions.
To calculate percentages between two limits I strongly advise you to use either the PERCENTILE function in Excel or to do a transformation on your data to improve normality. e.g. =PERCENTILE(C3:C1411, 0.25) equals 0.5179, meaning that 25% of your data is below 0.5179. Clearly, replacing 0.25 by 0.50 in the percentile function will give the same value as the median. e.g. It is also clear that 25% of the data is contained between 0.5179 and the median 0.68.
Transforming your data might be another possibility. I tried it on your data, using the LOG10 function in Excel, but this results in some problems because you have a few ‘0’ values in your list. If you omit them, then indeed, the data becomes normally distributed and you can play with the NORMINV and NORMDIST functions.
Anyhow, as said in previous posts, making a Histogram of your data, choosing the right BIN classes (in your case I should use classes of width 0.2 (that is put in cells the values 0, 0.2, 0.4, 0.6, up to 2.4) and enter this range of cells in the BIN range for the histogram macro in the analysis toolpak. You then will clearly see that your histogram is right-skewed. Do the log-transformation, choose another bin-range and plot the histogram again for the log-transformed data and you will see that the symmetry in your histogram clearly improved.

By the way, don’t be too impressed, this should be routine for me as I am a Statistician for profession and I like to play around in Excel for data analysis things.

Reply | Quote

I want to thank everyone for their posts. I have learnt a lot! I will play around with the data some more at leisure, but I think it is normally distributed with a right skew (which is what I had expected).

Reply | Quote