# Thread: calculating average based on a summary

1. ## calculating average based on a summary

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.  Reply With Quote

2. 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))`  Reply With Quote

3. 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.  Reply With Quote

4. Here you go....  Reply With Quote

5. Originally Posted by NBVC Here you go....
Thank you very much for your help  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•