View original (French)

Average age calculation in Excel

Solved
Braam23 Posted messages 5 Status Member -  
Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   -
Hello everyone
I have a little issue regarding an Excel operation; I would like to calculate an average age but, not knowing all the features of this program, I am calling upon your expertise
Here is the image of the data
Best regards

2 answers

  1.
    Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    To obtain the exact average age of the population, you would need the list of the 142,721 residents of Brest along with their ages.
    An approximate result will be provided by the formula
    =SUMPRODUCT(B3:G3;B2:G2)/SUM(B2:G2)
    after entering in B3:G3 the median age of each age group, namely
    7, 22, 37, 52, 67, and 82.
    The result is then 37.8 years!

    I hope the National Education will be satisfied with this ...

    Best regards.
    --
    Retirement is great! Especially in the West Indies... :-)
    Raymond (INSA, AFPA, CF/R)
    2
    1. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      Attention: if the oldest is only 90 years old, the result is quite close to reality.
      But if there is a high proportion of centenarians in the last age group, it skews the result. More specifically:

      Our formula considered that the last age bracket went from 75 to 89 years.
      - We found 37.8 years.
      If we take the more plausible assumption that it goes up to 99 years,
      - the result becomes 38.3 years.
      And if we had a city with people up to 109 years old,
      - the average would reach 38.7 years!
      0