Array Formula Help
 

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

Array Formula Help
 

if there is an error

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

--
Jim
"rmeister" wrote in
message ...
|
| 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
|

Array Formula Help
 

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,Sh eet1!$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" wrote in message
...

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.