Array Formula Help

[rmeister]

I am using an array formula that looks like this:

=AVERAGE(IF(Sheet1!$F$2:$F$65536=Sheet2!$A2,Sheet1!$AE$2:$AE$5128))

The formula is working great. The problem is that if there is an error then I get the #DIV/0!.

Is there a way I can embed something in this formula that would return 0 if error.

[Jim Rech]

>>if there is an error

You might have to address the errors. Can't you trap them?

--
Jim
"rmeister" <[email protected]> wrote in
message news:[email protected]...
|
| I am using an array formula that looks like this:
|
| =AVERAGE(IF(Sheet1!$F$2:$F$65536=Sheet2!$A2,Sheet1!$AE$2:$AE$5128))
|
| The formula is working great. The problem is that if there is an error
| then I get the #DIV/0!.
|
| Is there a way I can embed something in this formula that would return
| 0 if error.
|
|
| --
| rmeister
| ------------------------------------------------------------------------
| rmeister's Profile:
http://www.excelforum.com/member.php...o&userid=30163
| View this thread: http://www.excelforum.com/showthread...hreadid=528607
|

[David McRitchie]

See Tom Ogilvy's reply to a similar question
http://groups.google.com/group/micro...ba9e0295b0b1e9

Google Groups search:
array average DIV/0 group:*excel*

=AVERAGE(IF(Sheet1!$F$2:$F$65536=Sheet2!$A2,Sheet1!$AE$2:$AE$5128))

would be recoded as
=IF(ISRRROR(AVERAGE(IF(Sheet1!$F$2:$F$65536=Sheet2!$A2,Sheet1!$AE$2:$AE$5128))
),"",AVERAGE(IF(Sheet1!$F$2:$F$65536=Sheet2!$A2,Sheet1!$AE$2:$AE$5128)) )




HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"rmeister" <[email protected]> wrote in message
news:[email protected]...
>
> I am using an array formula that looks like this:
>
> =AVERAGE(IF(Sheet1!$F$2:$F$65536=Sheet2!$A2,Sheet1!$AE$2:$AE$5128))
>
> The formula is working great. The problem is that if there is an error
> then I get the #DIV/0!.
>
> Is there a way I can embed something in this formula that would return
> 0 if error.