Range of ages shown in bar graph

[Guest]

I have a range of 60 different ages and would like to know how to make a
graph (bar and line) that dipicts them in 5 or 10 year increments rather than
individually. How can I do this?

 

[Jon Peltier]

Let's assume your ages are in A1:A60. In B2:B11, enter the numbers 10, 20,
.... 100 (that should be enough, eh?), and put "Older" into B12. Select
C2:C12 with C2 as the active cell, type this formula

=FREQUENCY(A1:A60,B2:B11)

and hold CTRL+SHIFT when you press Enter, so you enter it as an array
formula. The resulting values show C2: number of values less than or equal
to 10, C3: number of values greater than 10 and less than or equal to 20,
etc.

Now you can make a column chart (i.e., histogram) with the data in columns B
and C.

This is covered in these web pages:

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

and Mike Middleton shows how to make nice histograms without too much work
here:

http://www.treeplan.com/better.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

 

[Guest]

Thank you that was helpful!

Can I change the X axis to reflect the range I selected (10-20, 20-30,
etc.)? Right now it just numbered the categories 1-7. Thank you!

 

[Jon Peltier]

In another range, say D212 (or rearrange this to put it to the left of the
FREQ formulas), enter the labels you want. For the first and last, use <10
and >100. For the ones in between you can use something like this in D3:

=B2&"-"&B3

and drag this down to fill D311.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______