calculating average based on a summary

A

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.
 
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))
 
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.
 
Here you go....
 

Attachments

  • Book1.xls
    23 KB · Views: 10
You must log in or register to reply here.
Back
Top