Average formula multiple worksheets



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!




Bernie Deitrick


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

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


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

MS Excel MVP

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question