Average of Vlookup data
 

I have 263 employees and I am trying to average out third quarter, so I have
tabs July, Aug, and Sept and a fourth tab for the average. I have built an
average formula using vlookup (by their employee id) to simplyfy it, BUT I am
getting an error (#N/A) for about 50 employees b/c they were only here for
one or two months... The problem is that I do not know which month without
looking through all of the data for each month.

Is there a formula that could look up this info for me and average it while
neglecting the missing data?

Current formula:
=AVERAGE(VLOOKUP(A6,JulGen,4,FALSE),VLOOKUP(A6,Aug Gen,4,FALSE),
VLOOKUP(A6,SeptGen,4,FALSE))

Average of Vlookup data
 

Change each of the VLOOKUP's to this (using the appropriate table name for
each):

IF(ISNA(VLOOKUP(A6,JulGen,4,0)),{""},VLOOKUP(A6,Ju lGen,4,0))

--
Biff
Microsoft Excel MVP


"JAbels001" wrote in message
...
I have 263 employees and I am trying to average out third quarter, so I
have
tabs July, Aug, and Sept and a fourth tab for the average. I have built an
average formula using vlookup (by their employee id) to simplyfy it, BUT I
am
getting an error (#N/A) for about 50 employees b/c they were only here for
one or two months... The problem is that I do not know which month without
looking through all of the data for each month.

Is there a formula that could look up this info for me and average it
while
neglecting the missing data?

Current formula:
=AVERAGE(VLOOKUP(A6,JulGen,4,FALSE),VLOOKUP(A6,Aug Gen,4,FALSE),
VLOOKUP(A6,SeptGen,4,FALSE))

Average of Vlookup data
 

That worked perfectly! You're a genius! Thanks!

"T. Valko" wrote:

Change each of the VLOOKUP's to this (using the appropriate table name for
each):

IF(ISNA(VLOOKUP(A6,JulGen,4,0)),{""},VLOOKUP(A6,Ju lGen,4,0))

--
Biff
Microsoft Excel MVP


"JAbels001" wrote in message
...
I have 263 employees and I am trying to average out third quarter, so I
have
tabs July, Aug, and Sept and a fourth tab for the average. I have built an
average formula using vlookup (by their employee id) to simplyfy it, BUT I
am
getting an error (#N/A) for about 50 employees b/c they were only here for
one or two months... The problem is that I do not know which month without
looking through all of the data for each month.

Is there a formula that could look up this info for me and average it
while
neglecting the missing data?

Current formula:
=AVERAGE(VLOOKUP(A6,JulGen,4,FALSE),VLOOKUP(A6,Aug Gen,4,FALSE),
VLOOKUP(A6,SeptGen,4,FALSE))

Average of Vlookup data
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JAbels001" wrote in message
...
That worked perfectly! You're a genius! Thanks!

"T. Valko" wrote:

Change each of the VLOOKUP's to this (using the appropriate table name
for
each):

IF(ISNA(VLOOKUP(A6,JulGen,4,0)),{""},VLOOKUP(A6,Ju lGen,4,0))

--
Biff
Microsoft Excel MVP


"JAbels001" wrote in message
...
I have 263 employees and I am trying to average out third quarter, so I
have
tabs July, Aug, and Sept and a fourth tab for the average. I have built
an
average formula using vlookup (by their employee id) to simplyfy it,
BUT I
am
getting an error (#N/A) for about 50 employees b/c they were only here
for
one or two months... The problem is that I do not know which month
without
looking through all of the data for each month.

Is there a formula that could look up this info for me and average it
while
neglecting the missing data?

Current formula:
=AVERAGE(VLOOKUP(A6,JulGen,4,FALSE),VLOOKUP(A6,Aug Gen,4,FALSE),
VLOOKUP(A6,SeptGen,4,FALSE))