IF(IS ERROR with Array Formula

[JOHANNA]

I have an array formula that calculates the average of a column if two other columns meet a certain criteria (company department and year).

=Average(IF(dBaseMaster!$DO$2:$DO$81="HR",IF(Year(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81))),0,Average(IF(dBaseMaster!$DO$2:$DO$81="HR",IF(Year(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81)))

I get a DIV/0 error when the criteria isn't met. I tried to add an IF(IS ERROR in the front of the formula, but it says that there is an error and highlights the ,0, in the middle of the formula.

Any ideas?

 

[T. Valko]

I have an array formula that calculates the average of a column if two other columns meet a certain criteria (company department and year).

=Average(IF(dBaseMaster!$DO$2:$DO$81="HR",IF(Year(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81))),0,Average(IF(dBaseMaster!$DO$2:$DO$81="HR",IF(Year(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81)))

I get a DIV/0 error when the criteria isn't met. I tried to add an IF(IS ERROR in the front of the formula, but it says that there is an error and highlights the ,0, in the middle of the formula.

Any ideas?

What version of Excel are you using?

 

[JOHANNA]

2003

 

[JOHANNA]

Okay, I feel like an idiot. I had a parenthesis in the wrong spot. Here is the correction if anyone finds it helpful.

=IF(ISERROR(AVERAGE(IF(dBaseMaster!$DO$2:$DO$81="SC-I",IF(YEAR(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81)))),0,(AVERAGE(IF(dBaseMaster!$DO$2:$DO$81="SC-I",IF(YEAR(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81)))))


This is an array so CTRL + SHIFT + Enter to calculate

 

[T. Valko]

Okay, I feel like an idiot. I had a parenthesis in the wrong spot. Here is the correction if anyone finds it helpful.

=IF(ISERROR(AVERAGE(IF(dBaseMaster!$DO$2:$DO$81="SC-I",IF(YEAR(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81)))),0,(AVERAGE(IF(dBaseMaster!$DO$2:$DO$81="SC-I",IF(YEAR(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81)))))


This is an array so CTRL + SHIFT + Enter to calculate

Since you want a result of 0 this is more efficient and a few keystrokes shorter:

Still array entered.

=LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(dBaseMaster!$DO$2:$DO$81="SC-I",IF(YEAR(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81)))))

 

[Aladin Akyurek]

I have an array formula that calculates the average of a column if two other columns meet a certain criteria (company department and year).

=Average(IF(dBaseMaster!$DO$2:$DO$81="HR",IF(Year(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81))),0,Average(IF(dBaseMaster!$DO$2:$DO$81="HR",IF(Year(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81)))

I get a DIV/0 error when the criteria isn't met. I tried to add an IF(IS ERROR in the front of the formula, but it says that there is an error and highlights the ,0, in the middle of the formula.

Any ideas?

Try...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    AVERAGE(IF(dBaseMaster!$DO$2:$DO$81="HR",
      IF(YEAR(dBaseMaster!AH2:AH81)=2010,dBaseMaster!DN2:DN81)))))