Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Range of ages shown in bar graph
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? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Range of ages shown in bar graph
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 he 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 _______ "Lindsay" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Range of ages shown in bar graph
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" wrote: 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 he 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 _______ "Lindsay" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Range of ages shown in bar graph
In another range, say D2:D12 (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 D3:D11. - 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 _______ "Lindsay" wrote in message ... 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" wrote: 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 he 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 _______ "Lindsay" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically changing the range in a graph | Charts and Charting in Excel | |||
Data Validation range | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
Array to named range conversion... | Excel Discussion (Misc queries) |