Hi, all:
I want to build a chart that shows age of respondee on the x-axis and frequency of responses on the y-axis. This represents an age distribution. Probably best as a bar chart. How do I do this using Excel charts? Here is the table. Many thanks!
Age Count
35 39
38 38
36 35
33 34
30 33
28 32
29 32
40 32
34 31
31 30
32 29
26 23
41 22
42 20
37 19
43 19
27 18
39 18
24 16
44 16
25 14
45 14
46 13
23 10
49 8
50 8
47 7
52 6
48 5
55 5
53 3
54 3
59 3
22 2
51 2
63 2
57 1
71 1
75 1
I am using Excel 2008 for Mac
The first confusing thing is the data is sorted on frequency rather than age.
Using frequency would work if the categories where arbitary, like Apples, Oranges, Bananas etc, but with age there is already an inherent order in the data.
So rank the data on age.
Rather than a column chart I would use an area chart.
You could also change the category axis to Time Series so there is a proportional space between the data for age 63 and 71.
You will also need to add a dummy series to enable the axis to display meaningful ages.
This does have 1 draw back in that it suggest there is data for ages 64 thru 70 with a declining number of frequencies. While probably true it could be misleading.
So you need to expand the data range to include ages you have no data for.
If you stick with the column chart using Time series axis will not require you to add missing ages.
For some reason, I wasn't notified of this reply. It's most helpful. Thanks!
How can I rank the data by age using the two columns and keeping the cells linked so that I the count is connected to the age? Doh.
Thanks again.
Select both columns and then sort on age
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks