Array Formula Help

R

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 erro
then I get the #DIV/0!.

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

Jim Rech

if there is an error

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

--
Jim
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?action=getinfo&userid=30163
| View this thread: http://www.excelforum.com/showthread.php?threadid=528607
|
 
D

David McRitchie

See Tom Ogilvy's reply to a similar question
http://groups.google.com/group/microsoft.public.excel.misc/msg/8dba9e0295b0b1e9

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
 
Top