Average formula multiple worksheets

[JAbels001]

Alright... This has been stumping me for awhile and I dont know if there is
an easy way to do this, but I'm hoping someone smarter than me could help. I
have a workbook that contains 13 worksheets ("Average" and "Jan" thru "Dec").
My "Average" worksheet needs to find the values of a specific employee
(vlookup) for every month and then average their data... Now I wrote a
formula that accomplishes that; however sometimes an employee may not have
any data for a specific month or they may have an "N/a" because that month
didnt count for them. When that happens my formula comes back as either
"#N/A" or "#DIV/0". I'm looking for a formula that will simply exclude these
months from the average formula. Here is what I am using currently. Please
help if you know how to do this! Thanks!

=AVERAGE(IF(ISNA(VLOOKUP(B7,Jan,4,0)),{""},VLOOKUP(B7,Feb,4,0)),IF(ISNA(VLOOKUP(B7,Mar,4,0)),{""},VLOOKUP(B7,April,4,0)),IF(ISNA(VLOOKUP(B7,May,4,0)),{""},VLOOKUP(B7,June,4,0)),IF(ISNA(VLOOKUP(B7,July,4,0)),{""},VLOOKUP(B7,Aug,4,0)),IF(ISNA(VLOOKUP(B7,Sept,4,0)),{""},IF(ISNA(VLOOKUP(B7,Oct,4,0)),{""},IF(ISNA(VLOOKUP(B7,Nov,4,0)),{""},IF(ISNA(VLOOKUP(B7,Dec,4,0)),{""},"N/a")))))

 

[Bernie Deitrick]

J,

The general kernel could be this --
IF(NOT(ISNA(VLOOKUP(B7,Feb,4,0))),VLOOKUP(B7,Feb,4,0)) -- times twelve:

=AVERAGE(IF(NOT(ISNA(VLOOKUP(B7,Jan,4,0))),VLOOKUP(B7,Jan,4,0)),IF(NOT(ISNA(VLOOKUP(B7,Feb,4,0))),VLOOKUP(B7,Feb,4,0)),
10 more times)



But that is terrible design. So, why not create a table?

In B7, and down the column, put the employee, and in C6 and across, put Jan,
Feb, etc.

Then in C7, put the formula

=IF(NOT(ISERROR(VLOOKUP($B7,INDIRECT(C$6),4,0))),VLOOKUP($B7,INDIRECT(C$6),4,0))

and copy to match your values in column B and in row 6. Then average those
twelve formulas.

HTH,
Bernie
MS Excel MVP