demographic analysis

[katag]

Hi,

I have an overview of all employees and their age in the company. I want to count how many employees are for example in the interval 20-24, 25-29, 30-34 etc. Is there a forumula to do it? Can it be done in a pivot table?

katag

 

[Peter_SSs]

Welcome to MrExcel!

The answer to both your questions is 'Yes'.

Formula
Formula in F2 copied down.


Pivot Table
Create Pivot table with Age as Row labels and Name in the Value area then right click in the Age column and choose 'Group...' and group by 5's. Result shown in columns H:I

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Name	Age		Age Ranges		Count		Row Labels	Count of Name
2	Name 1	21		20	24	4		20-24	4
3	Name 2	33		25	29	2		25-29	2
4	Name 3	24		30	34	5		30-34	5
5	Name 4	26						Grand Total	11
6	Name 5	30
7	Name 6	33
8	Name 7	34
9	Name 8	21
10	Name 9	25
11	Name 10	33
12	Name 11	24
13
Age ranges

 

[scottylad2]

Frequency will also do as you need. the 95 as the upper age would count everyone who is older than your upper age limi

Excel Workbook
	G	H
37	20	0
38	24	4
39	29	2
40	34	5
41	95	0
Sheet3

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width:48pt" span="2" width="64"> <tr style="height:14.4pt" height="19"> <td class="xl63" style="height:14.4pt;width:48pt" height="19" width="64">
</td><td class="xl64" style="border-left:none;width:48pt" align="right" width="64">
</td></tr><tr style="height:14.4pt" height="19"><td class="xl63" style="height:14.4pt;border-top:none; width:48pt" height="19" width="64">
</td><td class="xl64" style="border-top:none;border-left:none" align="right">
</td></tr><tr style="height:14.4pt" height="19"><td class="xl63" style="height:14.4pt;border-top:none; width:48pt" height="19" width="64">
</td><td class="xl64" style="border-top:none;border-left:none" align="right">
</td></tr><tr style="height:14.4pt" height="19"><td class="xl63" style="height:14.4pt;border-top:none; width:48pt" height="19" width="64">
</td><td class="xl64" style="border-top:none;border-left:none" align="right">
</td></tr><tr style="height:14.4pt" height="19"><td class="xl65" style="height:14.4pt; border-top:none;width:48pt" align="right" height="19" width="64">
</td><td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> </table>