calculate average values for 100 records in the shortest way?

[CYLL]

if we are given birth date (eg 12-june-1993), we need to calculate th
average age as at 1 jan 2013. (note: if there are like 100 records o
different birth dates, how to calculate the 100 records in the shortes
possible method?
I know of one way.. that is to select the column which I place 1 ja
2013 MINUS the birth date and DIVIDE by 365.25... but if there are lik
100 records.. I cant possibly use this method one by one right??? i
there a short cut?

another question.. if to calculate the average length of service a
employee has been working in a company.. the joined date is given i
this format (eg 2-sep-1990) and there are like 100 records of differen
employees.. how to calculate??? note: in the computation allowed to us
30days = 1 mont

i'm not proficient in Microsoft excel.. so im trying to find out..
thanks

 

[MyVeryOwnSelf]

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.

 

[CYLL]

;1610464']> if we are given birth date (eg 12-june-1993), we need t
calculate the> average age as at 1 jan 2013. -

...
calculate the average length of service an> employee has been workin

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 roun
as needed .

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

Hi, thank you for your reply.

I tried "=TODAY()-ROUND(AVERAGE(A:A),0)" where A:A contains the joine
dates of lets say 100 different employees in the format of (dd-mm-yy).
pressed cltr-shift-enter (I tried enter but it gave me dd-mm-yy as th
final answer) and I got 4digit number.. which is the total averag
length in days right.. hmm. I just wonder what's the '0' at the en
stands for? thanks agai

 

[MyVeryOwnSelf]

I just wonder what's the '0' at the end stands for?

From Excel's built-in "Help" for the ROUND function:

ROUND(number, num_digits)
number: The number that you want to round.
num_digits: The number of digits to which you want to round
the number argument.

So num_digits=0 means "return a value having no digits after the decimal point." In our case, that's complete days with no extra hours or minutes. If we were dealing with dollars and cents, we might use 2 instead of 0.

 

[MyVeryOwnSelf]

I pressed cltr-shift-enter (I tried enter but it gave me dd-mm-yy

as the final answer)

Dates in Excel are really numbers, formatted differently. Using just "Enter" by itself actually gives the right answer, but to change the format select the cell and use
right-click > Format cells > number
to see the format choices.

By changing the format of the value 41353, for example, you can get it to look any of these ways and many more:
41353
03/20/13
41,353.000
4135300.00%
Wednesday, March 20, 2013
3/20/13 12:00 AM
4.14E+04
€41,353.00

Ragardless of the format, the value behaves the same way in arithmetic calculations.

 

[CYLL]

;1610498']> I pressed cltr-shift-enter (I tried enter but it gave m
dd-mm-yy-

as the final answer)-

Dates in Excel are really numbers, formatted differently. Using jus
"Enter" by itself actually gives the right answer, but to change th
format select the cell and use
right-click > Format cells > number
to see the format choices.

By changing the format of the value 41353, for example, you can get i
to look any of these ways and many mo
41353
03/20/13
41,353.000
4135300.00%
Wednesday, March 20, 2013
3/20/13 12:00 AM
4.14E+04
€41,353.00

Ragardless of the format, the value behaves the same way in arithmeti
calculations.

oh wow I didn't know about this! to think I even wanted to tr
cltr-shift-enter.. hahaha that will just give me another answer.

thank you so much!