Formula to group age ranges?

[moonietoons]

I'm doing a report that needs to group employees' ages into the following ranges:

Under 20
20-29
30-39
40-49
50-59
60-69
Over 70

All of my ages are in column B. I've tried doing a subtotal of each age, but can't figure out how to get subtotals by age GROUP. Anyone have advice?

Thanks so much.

 

[Norie]

Why not use a pivot table?

It should give you those groupings.

 

[moonietoons]

The reason is...

I have no idea what a Pivot Table is. I'm not NEARLY as knowledgeable as the rest of you; I lurk around hoping to learn by osmosis.

 

[Norie]

Well try going to Data>Pivot Table Report... and follow the wizard.

Add the age as a row item.

Can you post an example of your data and required result, perhaps using Colo's HTML Maker?

 

[moonietoons]

I tried the wizard, but...

It's not a very good one. I can't download the add-in (I'm at work at they FREAK about that sort of thing.) This is what I have:

A B C
Age Division Job Class

18 Division Job Class
37 Numbers Numbers
45
62
22
22
36


What I need is:

A B C
Age Division Job Class

18
18
18
19
19
Total under 20 = 5

22
25
25
26
25
28
28
Total 20-29 = 7

(And so on.)

Does that make sense?

Thanks,

 

[Mark W.]

Book1
	A	B	C	D
1	Age Group	Age Division
2	Under 20	18
3	Under 20	18
4	Under 20	18
5	Under 20	19
6	Under 20	19
7	Under 20 Total	5
8	20-29	22
9	20-29	25
10	20-29	25
11	20-29	26
12	20-29	25
13	20-29	28
14	20-29	28
15	20-29 Total	7
16	Grand Total	13
17
Sheet1

 

[Norie]

So you want to count how many people in an age range are in a division/class/job?

Try this.

Select the range with your data and goto Data>PivotTable...

Add Age as a row item and (sum(or count) of) division, class and job as data items.

Once you have finished the pivot table should display the data for each individual group.

To group the ages right click on the age field and select Group and Outline>Group...

That should give you various options on how to group the ages.

Give it a try and play about with it till you get the required result.