Don't include errors in average

[Josh Kraemer]

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,'2007Flk'!
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.

 

[T. Valko]

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

 

[T. Valko]

Don't overlook the easiest way to do this in the first place........

Fix the errors then a simple =AVERAGE(.....) will work!

Biff