Results 1 to 10 of 10
  1. #1
    Star Lounger
    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 (0-0.1,0.1-0.2, etc.)? Thanks in advance.
    Attached Files Attached Files

  2. #2
    Cecil Rhoades
    Guest

    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.

  3. #3
    New Lounger
    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 (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.
    Attached Files Attached Files

  4. #4
    Star Lounger
    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

  5. #5
    Star Lounger
    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!!!)

  6. #6
    4 Star Lounger
    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 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

  7. #7
    Star Lounger
    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 w-a-y 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 2-3 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.

  8. #8
    4 Star Lounger
    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 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.

  9. #9
    Cecil Rhoades
    Guest

    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.

  10. #10
    Star Lounger
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •