EXCEL: Calculate average of multiple age ranges
Raymond PENTIER Posted messages 58548 Registration date Status Contributeur Last intervention -
Hello everyone,
I am currently working on a data series in Excel and I am facing a challenge with calculating the average age of the age groups. I have several subjects and each one is in an age group:
Under 20 years; 22-23 years; 23-25 years; Over 25 years.
How can I automatically calculate the average age of the subjects?
Thank you in advance :)
5 réponses
Good evening,
If you don't store the exact age of your subjects but only their age range, then you can't calculate the exact average age; you'll have an average age... based on average ages. If that's not a snake biting its own tail, then tell me!
You'll probably need to define arbitrary rules or not, depending on the constraints you already have, for example, what is the maximum age allowed for a person... 100 years, 150 years, +infinity?
- If it's a list of employees, maybe the minimum will be something like 16 or 18 years, and the maximum relatively close to the retirement age, etc., etc.
In which case, you just need to take the average of each range to calculate the average...
So by default:
- Any person in the range Less than 20 years (strictly or not?) would be 9 and a half years with 0 as min
- Any person in the range 22-23 years (inclusive or exclusive interval?) would be 22 and a half years
- Any person in the range 23-25 years (inclusive or exclusive interval?) would be 24 years
- Any person in the range More than 25 years (strictly or not?) would be 63 years with 100 years as max
And really, if you want to be realistic, you can use population age curves, etc., etc., to apply a coefficient, but then it would be more about probabilities than pure and hard math.
Otherwise, if I really didn't understand your question, I suggest showing with a short example of five people how the data is stored in your spreadsheet.
Best regards
Hello.
I think I sense a hint of disappointment in your last message ...
So what exactly did you mean by :
"How can I automatically calculate the average age of the subjects?"
Because each of us has tried to understand and interpret your request, which is really not clear!
Retirement is nice! Especially in the Caribbean...
Raymond (INSA, AFPA)
@Apogee01
Hello,
If you haven't seen it, I have provided an answer to your request from 05/08:
https://forums.commentcamarche.net/forum/affich-36070133-calculer-la-moyenne-de-deux-series-tranches-d-age-et
Best regards.
Good evening urilou777,
I greatly appreciate you taking the time to respond to me, especially with so much detail. I completely understand you. However, I am sending you an example of the document anyway.
I look forward to your reply.
Thank you again!
Here is the document: https://www.cjoint.com/c/LHfaHZ1QVVC