Bar chart with Y Axes Categories
 

I have very little experience with charts, so I really appreciate your help.

I have a column of employees' "Years until retirement" (approx 139
employees). I'd like a chart that shows a bar for 0-5 years, 6-9 years,
10-14 years, 15-19 years, 20-24 years, 25-29 years and 30+.

How do I categorize to get this chart?

THANK YOU.

MB

Bar chart with Y Axes Categories
 

I think you want the categories on the X axis. In the first column of the
sheet, put your year ranges, and in the second column, put the number of
employees within each range. Make a chart from this data.

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


"MB" wrote in message
...
I have very little experience with charts, so I really appreciate your
help.

I have a column of employees' "Years until retirement" (approx 139
employees). I'd like a chart that shows a bar for 0-5 years, 6-9 years,
10-14 years, 15-19 years, 20-24 years, 25-29 years and 30+.

How do I categorize to get this chart?

THANK YOU.

MB

Bar chart with Y Axes Categories
 

Does that mean I need a formula to sort those ages?

Column A = employee name; Column B = hire date; Column C = birth dates
Column D = current age (a formula) and Column E = number of years until
retirement, based on age 65. So, formula in that column is =SUM(65-D1).

Thanks, Jon.
--
MB


"Jon Peltier" wrote:

I think you want the categories on the X axis. In the first column of the
sheet, put your year ranges, and in the second column, put the number of
employees within each range. Make a chart from this data.

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


"MB" wrote in message
...
I have very little experience with charts, so I really appreciate your
help.

I have a column of employees' "Years until retirement" (approx 139
employees). I'd like a chart that shows a bar for 0-5 years, 6-9 years,
10-14 years, 15-19 years, 20-24 years, 25-29 years and 30+.

How do I categorize to get this chart?

THANK YOU.

MB

Bar chart with Y Axes Categories
 

Hi,

Create a new column and enter the upper age for each group for the age groups:
5
9

and so on, say starting in column F1. Next to that add another column and
enter the following formula into the first cell to the right of the 0-5 group:

Highlight all the cells in the colum G which are next to all of the ages you
entered into column F. Type the formula =FREQUENCY(E1:E139,F1) and press
Shift Ctrl Enter to enter this formula.

This will give the count or frequency by age group. You then chart that
range.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"MB" wrote:

Does that mean I need a formula to sort those ages?

Column A = employee name; Column B = hire date; Column C = birth dates
Column D = current age (a formula) and Column E = number of years until
retirement, based on age 65. So, formula in that column is =SUM(65-D1).

Thanks, Jon.
--
MB


"Jon Peltier" wrote:

I think you want the categories on the X axis. In the first column of the
sheet, put your year ranges, and in the second column, put the number of
employees within each range. Make a chart from this data.

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


"MB" wrote in message
...
I have very little experience with charts, so I really appreciate your
help.

I have a column of employees' "Years until retirement" (approx 139
employees). I'd like a chart that shows a bar for 0-5 years, 6-9 years,
10-14 years, 15-19 years, 20-24 years, 25-29 years and 30+.

How do I categorize to get this chart?

THANK YOU.

MB

Bar chart with Y Axes Categories
 

Yes, the data needs some processing first.

The formula in column E only needs to be =65-D1. A lot of people learn to
insert a formula using the big sum button on the toolbars, which inserts
=SUM() and lets you select cells or type something in the parentheses. This
leads people to believe that SUM is needed for any formula, but it's not
necessary.

In a practice worksheet, I set up a range like your A:E. In H1:K8 I set up a
little table:

Lower Upper Range Count
0 5 0-5 3
6 10 6-10 4
11 15 11-15 5
16 20 16-20 6
21 25 21-25 8
26 30 26-30 4
31 31+ 0

The first three columns are easy. Cell K2 contains this array formula:

=SUM(IF($E$2:$E$37=H2,IF($E$2:$E$37<=I2,1,0),0))

where E2:E37 is the range containing the years until retirement (change it
to match yours). This formula is not entered simply by pressing Enter:
instead hold Ctrl+Shift while pressing Enter, which makes it an array
formula. If done correctly, Excel will put {curly brackets} around the
formula. Fill this formula down to K7, and in K8 enter this shorter array
formula:

=SUM(IF($E$2:$E$37=H8,1,0))

Hold Ctrl+Shift while pressing Enter.

Now select the data in columns J and K and create a column chart. The range
column gives the category labels and the Count column gives the column
heights.

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


"MB" wrote in message
...
Does that mean I need a formula to sort those ages?

Column A = employee name; Column B = hire date; Column C = birth dates
Column D = current age (a formula) and Column E = number of years until
retirement, based on age 65. So, formula in that column is =SUM(65-D1).

Thanks, Jon.
--
MB


"Jon Peltier" wrote:

I think you want the categories on the X axis. In the first column of the
sheet, put your year ranges, and in the second column, put the number of
employees within each range. Make a chart from this data.

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


"MB" wrote in message
...
I have very little experience with charts, so I really appreciate your
help.

I have a column of employees' "Years until retirement" (approx 139
employees). I'd like a chart that shows a bar for 0-5 years, 6-9
years,
10-14 years, 15-19 years, 20-24 years, 25-29 years and 30+.

How do I categorize to get this chart?

THANK YOU.

MB

Bar chart with Y Axes Categories
 

Wonderful! THANK YOU! You guys are SO appreciated!
--
MB


"Jon Peltier" wrote:

Yes, the data needs some processing first.

The formula in column E only needs to be =65-D1. A lot of people learn to
insert a formula using the big sum button on the toolbars, which inserts
=SUM() and lets you select cells or type something in the parentheses. This
leads people to believe that SUM is needed for any formula, but it's not
necessary.

In a practice worksheet, I set up a range like your A:E. In H1:K8 I set up a
little table:

Lower Upper Range Count
0 5 0-5 3
6 10 6-10 4
11 15 11-15 5
16 20 16-20 6
21 25 21-25 8
26 30 26-30 4
31 31+ 0

The first three columns are easy. Cell K2 contains this array formula:

=SUM(IF($E$2:$E$37=H2,IF($E$2:$E$37<=I2,1,0),0))

where E2:E37 is the range containing the years until retirement (change it
to match yours). This formula is not entered simply by pressing Enter:
instead hold Ctrl+Shift while pressing Enter, which makes it an array
formula. If done correctly, Excel will put {curly brackets} around the
formula. Fill this formula down to K7, and in K8 enter this shorter array
formula:

=SUM(IF($E$2:$E$37=H8,1,0))

Hold Ctrl+Shift while pressing Enter.

Now select the data in columns J and K and create a column chart. The range
column gives the category labels and the Count column gives the column
heights.

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


"MB" wrote in message
...
Does that mean I need a formula to sort those ages?

Column A = employee name; Column B = hire date; Column C = birth dates
Column D = current age (a formula) and Column E = number of years until
retirement, based on age 65. So, formula in that column is =SUM(65-D1).

Thanks, Jon.
--
MB


"Jon Peltier" wrote:

I think you want the categories on the X axis. In the first column of the
sheet, put your year ranges, and in the second column, put the number of
employees within each range. Make a chart from this data.

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


"MB" wrote in message
...
I have very little experience with charts, so I really appreciate your
help.

I have a column of employees' "Years until retirement" (approx 139
employees). I'd like a chart that shows a bar for 0-5 years, 6-9
years,
10-14 years, 15-19 years, 20-24 years, 25-29 years and 30+.

How do I categorize to get this chart?

THANK YOU.

MB

Bar chart with Y Axes Categories
 

Hi Jon,

The Frequency function is easier to use in this situation because you only
need to enter the upper value of each bin, and you can (must) do it as a
number. Second there is only a need to enter one function not two different
versions.

I would strongly recommend that the user learn how to use this function, if
for no other reason than, it has a number of applications.

--
Cheers,
Shane Devenshire


"MB" wrote:

Wonderful! THANK YOU! You guys are SO appreciated!
--
MB


"Jon Peltier" wrote:

Yes, the data needs some processing first.

The formula in column E only needs to be =65-D1. A lot of people learn to
insert a formula using the big sum button on the toolbars, which inserts
=SUM() and lets you select cells or type something in the parentheses. This
leads people to believe that SUM is needed for any formula, but it's not
necessary.

In a practice worksheet, I set up a range like your A:E. In H1:K8 I set up a
little table:

Lower Upper Range Count
0 5 0-5 3
6 10 6-10 4
11 15 11-15 5
16 20 16-20 6
21 25 21-25 8
26 30 26-30 4
31 31+ 0

The first three columns are easy. Cell K2 contains this array formula:

=SUM(IF($E$2:$E$37=H2,IF($E$2:$E$37<=I2,1,0),0))

where E2:E37 is the range containing the years until retirement (change it
to match yours). This formula is not entered simply by pressing Enter:
instead hold Ctrl+Shift while pressing Enter, which makes it an array
formula. If done correctly, Excel will put {curly brackets} around the
formula. Fill this formula down to K7, and in K8 enter this shorter array
formula:

=SUM(IF($E$2:$E$37=H8,1,0))

Hold Ctrl+Shift while pressing Enter.

Now select the data in columns J and K and create a column chart. The range
column gives the category labels and the Count column gives the column
heights.

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


"MB" wrote in message
...
Does that mean I need a formula to sort those ages?

Column A = employee name; Column B = hire date; Column C = birth dates
Column D = current age (a formula) and Column E = number of years until
retirement, based on age 65. So, formula in that column is =SUM(65-D1).

Thanks, Jon.
--
MB


"Jon Peltier" wrote:

I think you want the categories on the X axis. In the first column of the
sheet, put your year ranges, and in the second column, put the number of
employees within each range. Make a chart from this data.

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


"MB" wrote in message
...
I have very little experience with charts, so I really appreciate your
help.

I have a column of employees' "Years until retirement" (approx 139
employees). I'd like a chart that shows a bar for 0-5 years, 6-9
years,
10-14 years, 15-19 years, 20-24 years, 25-29 years and 30+.

How do I categorize to get this chart?

THANK YOU.

MB