Cumulative Frequency

[Guest]

Hi, I need to produce a cumulative frequency chart with the following data. I
am new to this concept on excel so any help will be grately appreciated. here
is the data:

Sentence Length Frequency Cumulative Frequency
1-10 9 9
11-20 12 21
21-30 15 44
31-40 8 49
41-50 1 50


Any help would be appreciated. I hope that i have made myself clear!
Thanks a lot,
Chris Grant

 

[Guest]

Chris,

To set up your chart, add a fourth column called “Cumulative % Frequencyâ€.
For this example, assume that the top left title starts in cell A1 and the
range of titles and data goes from cell A1 to D7. A minor note - I think you
have an addition error for the Cumulative Frequency in the 21-30 age groups.
I’ve corrected it here.

The cumulative frequency is calculated as each value in column C divided by
45 (the total cumulative frequency). For example, 21/45 = .47, 36/45 = .80,
etc.

Your source data should look like this:

A B C D
Sentence Cumulative Cumulative
Length Frequency Frequency % Frequency
1-10 9 9 20%
11-20 12 21 47%
21-30 15 36 80%
31-40 8 44 98%
41-50 1 45 100%

The data to chart is in columns A, B, and D. To build the chart, put your
curser in cell A3 (the cell with the title called “Length†in it). Select
the range A38. Go to the chart wizard and choose the Column -> Clustered
Column chart option. Go through the rest of the prompts until the chart
appears.

Click once on the tallest set of bars and delete the series by hitting the
delete key.

Click once on the smallest set of bars and in the Standard Toolbar hit Chart
-> Chart Type -> Standard Types Tab -> Line Chart Option. Hit the OK button.
When this step is complete you should have a chart with one set of columns
and a line.

Next, click once on the series that looks like a line. Right-click on your
mouse, hit the Selected Object option. In the Format Data Series dialog box,
go to the axis tab and choose the “Plot series on secondary axis optionâ€.
When this step is complete, your chart should have a percentage axis on the
right side and a frequency axis on the left side.

Double-click on the secondary (right side) Y axis values. In the Format
Axis dialog box go to the scales tab and set the maximum to 1.

Complete the remainder of any formatting as you see fit.

 

[Jon Peltier]

Chris -

Check out this web page to see how to set up a frequency table:

http://peltiertech.com/Excel/Charts/Histograms.html

The Analysis Toolpak approach (described earlier in the page) gives you
frequency and cumulative % frequency. But I'd do it manually (skip down
a ways on the page), and use another column to generate a running sum,
because you didn't mention percentages. If your frequency data are in
cells B2 to B-whatever, put this formula into C2:

=sum(B$2:B2)

and fill it down column C as far as needed.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______