Calculate average age of football players.

Hi,

In E2 and copy down:

=DATEDIF(C2,D2,"y")&" years "&DATEDIF(C2,D2,"ym")&" months "&DATEDIF(C2,D2,"md")&" days"

Good luck with the translation!

Regards

@siggyfloyd

Are you sure of your logic?

How old today is a player born on 19/07/1988? Or one born on 05/07/1984?

Regards

A person can have an exact age expressed in years, months, and days, but:

1. You can't use that to compare the age of two players

2. You can't average those values in any meaningful way.

"A person can have an exact age expressed in years, months, and days, but:

1. You can't use that to compare the age of two players

2. You can't average those values in any meaningful way."

True, but there's nothing stopping you calculating the average age in days, then returning that average in the form "years, months, and days".

Regards

...or do what Rambo4711 does in his attachment. he uses the same DATEDIF formula but uses the average of the DOBs as first date. That would be my prefered method

there's nothing stopping you calculating the average age in days, then returning that average in the form "years, months, and days".

Yes, but only meaningfully if they were all born on the same day, which seems unlikely.

Why "not meaningful" shg? Agreed there's no definitive "correct answer" when it comes to the exact number of days/months but surely the concept of "average age" is OK? Or are you saying that it would be better expressed differently?

If the average age of players is 32 days (they start younger and younger), what's their average age in months and days? It depends on the date you start.

I think we're looking at in two different ways.

Of course, rigorously, shg is correct, in that there's no way to arrive at a strictly 'correct' answer to the question of finding an 'average age' for several people.

But then, how often do we employ the term 'average' in a way which, though perhaps strictly unsound, nevertheless has 'meaning'? "It rains on average nine months of the year in Seattle" would be unlikely to get published in Nature, but, by other standards, it's still a statistic that, nevertheless, has 'meaning'.

Regards

"If the average age of players is 32 days (they start younger and younger), what's their average age in months and days? It depends on the date you start."

Even then, you could use a statistical approximation to the average number of days in a month to give a reasonable answer.

Depending on over what timescale you take that average, I don't see why, with an average of e.g. 1 month = 30.1 days, you couldn't use that to give "0 years, 1 month and 2 days" in this example.

Regards

Sure, except that no month has 30.4 days. Decimal years would seem to me to be less inexact.

Seems like you could just use

=TODAY() - AVERAGE(A2:A24)

and format as yy" years "m" months "d" days"

What doesn't work?

Originally Posted by shg

....and format as yy" years "m" months "d" days"

The months are always a problem in that format, always one too high (never shows zero but does show 12) because "m" is month of the year. And if you use 1904 date format the days and years will also be out!

I don't think there's a simple way to show durations in Y/M/D format - you either have to use DATEDIF or revert to some other measurement like fractional years or days.

Originally Posted by daddylonglegs

The months are always a problem in that format, always one too high (never shows zero but does show 12) because "m" is month of the year.

Good catch, DO, thanks. How about

=EDATE(TODAY() - AVERAGE(C2:C24), -1) + 1

... formatted as before.

And if you use 1904 date format the days and years will also be out,

That would change all of the hard-coded dates, but I take your point.

bladerider,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.