Make Histogram and Normal distribution.

[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

 

[bernard liengme]

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!)"

 

[Mike Middleton]

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!)"