Bar chart with Y Axes Categories

[Guest]

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

 

[Jon Peltier]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Jon Peltier]

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

 

[Guest]

Wonderful! THANK YOU! You guys are SO appreciated!

 

[Guest]

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.