How to count an age group

[Call me Ana, Ana Pego]

Sorry,
I am trying to do this operation:
in cell F7 I have the following datas corresponding to peoples age
how can I count the numbers of time its ocurrs eg. of 50-<70

50-<70
50-<70
17 <30
30-<50
70+
17 <30
I also need to know the percentage of each of them, how often they have been
in that cell.
I appreciate if any good soul can help me.
Cheers

 

[Max]

Hazarding a guess that a pivot table is quite ideal
for what you seek to do. It's easy n fast to set-up

Here's the play to tinker with (steps in xl2003):
Presume that you have age data (as posted) in col A,
with col header: Age (in A1), data from A2 down, viz:

Age
50-<70
50-<70
17 <30
30-<50
70+
17 <30
etc

Select any cell in the table, click Data > Pivot table ..
Click Next > Next. In step 3, click Layout, then just:
Drag n drop Age into ROW & DATA area
Click OK > Finish

Hop over to the pivot sheet (new sheet just to the left of your data sheet)
The pivot will return the uniques listing of the various items that's in the
Age source col (eg: 17 <30, 50-<70, etc) and the corresponding counts of each
next to it.

And if you want the counts expressed as percentages (of total in col)
Double-click on "Count of Age".
In the dialog: Click Options, then
Under "Show data as:", choose: % of column > OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik

 

[ShaneDevenshire]

Hi,

A pivot table's on solution, here is another:

To create the following summary table
16 1 8%
29 3 23%
50 3 23%
70 2 15%
100 4 31%

Enter the ages listed in A1:A5 (I accounted for people over 70 you don't
neet to)
Select the range B1:B5 (or B4) and type but do not enter the formula
=FREQUENCY(E1:E13,A1:A5)
Then press Ctrl+Shift+Enter
Here the ages are in E1:E13.
In cell C1 enter the formula:
=B1/SUM($B$1:$B$5) or B4
and copy it down.
Format this column to percent.

If you don't like using the values in column A you could put them somewhere
else and enter the ones you show in your email. but still use the range I
gave you.


If this helps, please click the Yes button.