Need to average a range AND calc a formula at once...

[S Davis]

Thanks for reading this post.

I have a range of 2000 sets of data. I am trying to find the average
age of this set of employees when they were hired. The employees age
is within Column G, their hire date is in Column H, and the date the
data was produced (so that this can be used in the future without
distorting the facts at the time of the data) is in E1.

This formula works for calculating one person's age at the time of
hire:

=G3-((E1-H3)/365))

(or, in english, =AGE-minus the difference between the date of the data
and the employees hire date, ie if an employee were hired 6 months from
now, this formula will show an age 6 months younger.

I need to somehow nest this inside a formula that will calculate, on
the fly, each person's age (out to 2000 people) and turn out an average
age of all the data when they were hired.

Is this possible within one cell?

 

[daddylonglegs]

Try

=AVERAGE(G3:G2000-(E1-H3:H2000)/365)

confirmed with CTRL+SHIFT+ENTER

note: the range shouldn't contain any blank rows..