calculating average based on a summary

**AllanMiller** · 2013-07-20, 11:47 PM

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 RANGE	COUNT
0 to 4 years	5460
5 to 9 years	6320
10 to 14 years	7610
15 to 19 years	8190
20 to 24 years	7350
25 to 29 years	5720
30 to 34 years	5885
35 to 39 years	7690
40 to 44 years	9230
45 to 49 years	9420
50 to 54 years	8285
55 to 59 years	6480
60 to 64 years	4335
65 to 69 years	3090
70 to 74 years	2525
75 to 79 years	1740
80 to 84 years	1020
85 years and over	635

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** · 2013-07-22, 02:10 PM

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** · 2013-07-22, 04:28 PM

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.