Make Histogram and Normal distribution.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have a problem. I have a set of data (from 108 people, all percentages)
which I would like to put into a histogram. I can do this manually but as it
is part of an Excel Tool would like to be able to do it automatically. I was
wondering if anyone could help me, possibly with a macro, which will create a
set of data which will give me 5% ranges (0-5, 5-10, 10-15% etc) and the
number of people which fall into that category (i.e frequency)

I'm hoping this will give me something ressembling a normal distribution and
so I would like to know if it is possible to interpolate the data to give a
smooth curve? I would also like to mark where the lowest 20% and highest
20% occur!

Please help if you can!

Many thanks,

Ed
 
Start by using Help to find out about the FREQUENCY function
Find how to use AVERAGE and STDEV to compute these statistics
Then make a plot of the results
Next learn how to put a normal distribution curve on top from
http://www.tushar-mehta.com/excel/charts/normal_distribution/

come back for more as needed

best wishes
Bernard (formerly for UK and useful with computers)

"Ed (from UK and useless with comuters!)"
 
Ed -

For the histogram, you have several choices:

(A) Excel's Histogram tool

(B) Array-entered FREQUENCY worksheet function for the frequency
distribution with a Column chart type for display

(C) my free Better Histogram add-in, available at www.treeplan.com

For a properly-scaled normal distribution on top of a histogram, you could
use a combination chart: Column chart type for the histogram vertical bars
and XY (Scatter) chart type for the normal curve.

The data for plotting the normal curve could be a list of X values in a
column with corresponding NORMDIST values in an adjacent column.

The Better Histogram tool produces a combination chart, but the XY (Scatter)
is used only for the horizontal axis. You could change its chart (Chart |
Source Data | Series) to refer to XY normal data on your worksheet.

- Mike
www.mikemiddleton.com

"Ed (from UK and useless with comuters!)"
 
Back
Top