J
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")))))
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")))))