Results 1 to 5 of 5

Thread: calculating average based on a summary

  1. #1

    calculating average based on a summary



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    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))


  3. #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.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Here you go....
    Attached Files Attached Files


  5. #5
    Quote Originally Posted by NBVC View Post
    Here you go....
    Thank you very much for your help

Posting Permissions

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