Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! =AVERAGE(IF(ISNA(VLOOKUP(B7,Jan,4,0)),{""},VLOOKUP (B7,Feb,4,0)),IF(ISNA(VLOOKUP(B7,Mar,4,0)),{""},VL OOKUP(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(B 7,Sept,4,0)),{""},IF(ISNA(VLOOKUP(B7,Oct,4,0)),{"" },IF(ISNA(VLOOKUP(B7,Nov,4,0)),{""},IF(ISNA(VLOOKU P(B7,Dec,4,0)),{""},"N/a"))))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average formula multiple worksheets
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))),VL OOKUP(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))),V LOOKUP($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 "JAbels001" wrote in message ... 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)),{""},VL OOKUP(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(B 7,Sept,4,0)),{""},IF(ISNA(VLOOKUP(B7,Oct,4,0)),{"" },IF(ISNA(VLOOKUP(B7,Nov,4,0)),{""},IF(ISNA(VLOOKU P(B7,Dec,4,0)),{""},"N/a"))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Cells in Multiple Worksheets | Excel Worksheet Functions | |||
Calculate Average Accross Multiple Worksheets | Excel Discussion (Misc queries) | |||
1 cell average across multiple worksheets | Excel Worksheet Functions | |||
=Average(if( formula using different worksheets | Excel Worksheet Functions | |||
Average Values / Multiple Worksheets | Excel Worksheet Functions |