Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Cumulative Frequency
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 |
#2
|
|||
|
|||
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. Ive 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 A3:D8. 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. ---- Regards, John Mansfield http://www.pdbook.com "Chris Grant" wrote: 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 |
#3
|
|||
|
|||
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/ _______ Chris Grant wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying frequency polygons in Excel (Statistics) | Charts and Charting in Excel | |||
Cumulative Total | Excel Discussion (Misc queries) | |||
frequency for each occurance | Excel Worksheet Functions | |||
need a formula for cumulative grade | Excel Worksheet Functions |