calculating average based on a summary

AllanMiller

New member
Joined
Jul 20, 2013
Messages
3
Reaction score
0
Points
0
Hi, I have a table of age data from stats Canada and I can't figure out how to calculate the average and median age range based on data like this:

AGE RANGECOUNT
0 to 4 years5460
5 to 9 years6320
10 to 14 years7610
15 to 19 years8190
20 to 24 years7350
25 to 29 years5720
30 to 34 years5885
35 to 39 years7690
40 to 44 years9230
45 to 49 years9420
50 to 54 years8285
55 to 59 years6480
60 to 64 years4335
65 to 69 years3090
70 to 74 years2525
75 to 79 years1740
80 to 84 years1020
85 years and over635

I want to be able to say, for example, the average age range is 45 to 49 years. Median would be good too. I just don't know how to write the formulas.

Your help is greatly appreciated.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
If you add a couple of helper columns, perhaps you can get the weighted average and median.

e.g.

Assuming your data is in A2:B19, then in C2 enter formula:

Code:
=--LEFT(A2,FIND(" ",A2)-1)

copied down. This will give the lower bound of each age group (we need a numeric representation to do the calcs).

Then in D2 add this formula:

Code:
=SUM(B$2:B2)

copied down. This gives a running total of the count.

Now to get the Weighted Average:

Code:
=INDEX(A2:A19,MATCH(SUMPRODUCT(B2:B19,C2:C19)/SUM(B2:B19),C2:C19))

and weighted Median:

Code:
=INDEX(A2:A19,MATCH(LOOKUP(SUM(B2:B19)/2,D2:D19,C2:C19),C2:C19))
 

AllanMiller

New member
Joined
Jul 20, 2013
Messages
3
Reaction score
0
Points
0
Thanks, NBVC. Almost everything worked. Median returned an age range, but I got an "#N/A" for the average calculation and I'm not sure how to troubleshoot the formula - if you see something I should change, please let me know. Thanks again.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Here you go....
 

Attachments

  • Book1.xls
    23 KB · Views: 10
Top