The ultimate goal is to show users, at a glance, if their data is normally distributed.

The first attempt is a kludge that plots the data in a frequency graph. Then, the observed mean and standard deviation are used to build a "normal curve" graph. The frequency chart is laid over the the normal-curve chart and put next to some key statistics. The frequency chart also colors positive bins green and negative bins red.

It looks like this: enter image description here

I understand the fallacy of this approach, but for now it's practical. What is a better way to approach this issue?

  • 1
    $\begingroup$ There's something wrong here: the normal curve overlaid on the bar chart does not agree with the bars. $\endgroup$
    – whuber
    Apr 30 '11 at 1:23
  • $\begingroup$ Correct, the curve is built using only the mean and std dev of the observed data and is meant to show what the data would look like if it were normal. The frequency bars are the actual observations. Given the statistics shown to the left of the chart indicating positive skewness and fat tails the point is made fairly well. There is more green than red and there are frequency bins above the normal curve tails. $\endgroup$
    – GollyJer
    May 2 '11 at 0:11
  • $\begingroup$ the problem goes beyond that: the curve is uniformly too high. You need to rescale it so its total area equals the total area of the bars. $\endgroup$
    – whuber
    May 2 '11 at 14:14
  • $\begingroup$ Gotcha. Yet another reason this isn't an ideal solution. $\endgroup$
    – GollyJer
    May 2 '11 at 17:45

A normal probability plot is an excellent way to compare an empirical distribution to a normal distribution. Its merits are that it clearly displays the nature of any deviations from normality: ideally, the points lie along the diagonal; vertical deviations from the diagonal depict deviations from normality. Its disadvantages are that many people do not know how to read it, so beware!

To create a normal probability plot in Excel, rank the data (with the RANK function) and convert them to a normal score via


where 'count' is the amount of data and 'rank' references a cell with the rank, as shown in the illustrations.

spreadsheet example

The formulas in this spreadsheet are

enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.