View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MyVeryOwnSelf[_3_] MyVeryOwnSelf[_3_] is offline
external usenet poster
 
Posts: 56
Default calculate average values for 100 records in the shortest way?

if we are given birth date (eg 12-june-1993), we need to calculate the average age as at 1 jan 2013.
...
calculate the average length of service an employee has been working in a company ... the joined date is given


If column A contains dates, then
=ROUND(AVERAGE(A:A),0)
is the average of the dates.

So if they're birthdays, then
=DATE(2013,1,1)-ROUND(AVERAGE(A:A),0)
is the average age as at 1 jan 2013, in days. Format this as a number. To get age in months or years, divide by the appropriate value and round as needed .

Likewise, if they are "joined" dates, then
=TODAY()-ROUND(AVERAGE(A:A),0)
is the average length of service, in days.