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.

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.

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.