Results 1 to 10 of 10

20010531, 22:31 #1
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Normal distribution of following range of values?
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 (00.1,0.10.2, etc.)? Thanks in advance.

20010601, 00:40 #2Cecil RhoadesGuest
Re: Normal distribution of following range of values?
I did not download the file, but I believe this is what you are looking for
Lower Upper Count
Bound Bound
0 10 2
10 20 4
20 30 6
Etc.
The formula in the Count column, assuming Data in col C, LB in Col E, UB in Col F and Count in col G.
{=SUM(($C$2:$C$26>E2)*($C$2:$C$26<=F2)*1)}
This is an array formula, use the <CTRL><SHIFT><ENTER> to enter formula. Do not type the curly brackets.
Then graph the frequency chart you just created.
I hope this is helpfull.

20010601, 01:58 #3
 Join Date
 Dec 2000
 Location
 Indiana
 Posts
 22
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Normal distribution of following range of values?
See return attachment.
I used the histogram tool from the analysis took pack (addin 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.

20010601, 05:54 #4
 Join Date
 Jan 2001
 Location
 Newcastle, New South Wales, Australia
 Posts
 81
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Normal distribution of following range of values?
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

20010601, 14:57 #5
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Normal distribution of following range of values?
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!!!)

20010601, 16:01 #6
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Normal distribution of following range of values?
There are several ways to check the normality (Gaussian distribution or bellshaped distribution) of your data. You can do a statistical test (e.g. ShapiroWilk test or KolmogorovSmirnov 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 QQplot 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

20010601, 17:44 #7
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Normal distribution of following range of values?
Whew! <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> Thanks, Hans, for that very detailed & meticulous explanation. Unfortunately, much of that went way over my head; my head is still spinning. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> I will struggle to implement the tiny portion I understood. Meanwhile, more responses are welcome, especially if they involve only 23 mouse clicks! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
Note to Moderators: I heartily recommend Hans for some recognition.

20010602, 08:10 #8
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Normal distribution of following range of values?
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 rightskewed (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 rightskewed. Do the logtransformation, choose another binrange and plot the histogram again for the logtransformed 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.

20010602, 19:01 #9Cecil RhoadesGuest
Re: Normal distribution of following range of values?
I have often asked those whom have been using/programming computers for ages why they do things the hard way. Now I know. Since I have been at it awhile, I feel comfortable with the methods I have used for ages while there are tools that, if I take the time to become familiar with them, will save me blood sweat and tears.

20010604, 23:11 #10
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Normal distribution of following range of values?
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).