Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Don't include errors in average
I have a sheet that averages numbers from another sheet (2007 Flk):
=AVERAGE('2007 Flk'!G4,'2007 Flk'!K4,'2007 Flk'!O4,'2007 Flk'!W4,'2007 Flk'!AA4,'2007 Flk'!AE4) The problem is that "2007 Flk" has some DIV/0 errors in it. So I tried the following formula but it didn't work: =AVERAGE(IF(ISNUMBER('2007Flk'!G4,'2007Flk'!P4,'20 07Flk'! AC4),'2007Flk'!G4,'2007Flk'!P4,'2007Flk'!AC4)) That formula/function seems to only work if it's not referencing another sheet. Can anyone help me get this formula to work? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Don't include errors in average
One way:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(ISNUMBER(CHOOSE({1,2,3},'2007FLK'!G4,' 2007FLK'!P4,Sheet1!AC4)),CHOOSE({1,2,3},'2007FLK'! G4,'2007FLK'!P4,'2007FLK'!AC4))) Why does your first formula contain more references than your second formula? Biff "Josh Kraemer" wrote in message ups.com... I have a sheet that averages numbers from another sheet (2007 Flk): =AVERAGE('2007 Flk'!G4,'2007 Flk'!K4,'2007 Flk'!O4,'2007 Flk'!W4,'2007 Flk'!AA4,'2007 Flk'!AE4) The problem is that "2007 Flk" has some DIV/0 errors in it. So I tried the following formula but it didn't work: =AVERAGE(IF(ISNUMBER('2007Flk'!G4,'2007Flk'!P4,'20 07Flk'! AC4),'2007Flk'!G4,'2007Flk'!P4,'2007Flk'!AC4)) That formula/function seems to only work if it's not referencing another sheet. Can anyone help me get this formula to work? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Don't include errors in average
Don't overlook the easiest way to do this in the first place........
Fix the errors then a simple =AVERAGE(.....) will work! Biff "T. Valko" wrote in message ... One way: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(ISNUMBER(CHOOSE({1,2,3},'2007FLK'!G4,' 2007FLK'!P4,Sheet1!AC4)),CHOOSE({1,2,3},'2007FLK'! G4,'2007FLK'!P4,'2007FLK'!AC4))) Why does your first formula contain more references than your second formula? Biff "Josh Kraemer" wrote in message ups.com... I have a sheet that averages numbers from another sheet (2007 Flk): =AVERAGE('2007 Flk'!G4,'2007 Flk'!K4,'2007 Flk'!O4,'2007 Flk'!W4,'2007 Flk'!AA4,'2007 Flk'!AE4) The problem is that "2007 Flk" has some DIV/0 errors in it. So I tried the following formula but it didn't work: =AVERAGE(IF(ISNUMBER('2007Flk'!G4,'2007Flk'!P4,'20 07Flk'! AC4),'2007Flk'!G4,'2007Flk'!P4,'2007Flk'!AC4)) That formula/function seems to only work if it's not referencing another sheet. Can anyone help me get this formula to work? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Function (include Blank Cells and Zeros) | Excel Discussion (Misc queries) | |||
omiting cells in average calcs | Excel Discussion (Misc queries) | |||
Ignore errors when calculation average of multiple ranges | Excel Worksheet Functions | |||
plotted Average | Charts and Charting in Excel | |||
What is this kind of average called? | Excel Worksheet Functions |