Ignore #N/A and #DIV Average Multiple Sheets

[ExcelDJR]

Hi , i am looking to average the same cell over multiple sheets in Excel 2003 and ignore cells containing N/A or DIV .


Let's say Cell A4 of sheet1:sheet3

A4 sheet1 = 10
A4 sheet2 = 20
A4 sheet3 =#N/A


Average must return 15

Thank you very much !

 

[FDibbins]

Correct the formulas that produce the error cells

 

[ExcelDJR]

The formula of the cells returning errors i am looking to average are like this

=IF(COUNTIF(Q3585:Q3849,A3887),AVERAGE(IF(Q3585:Q3849=A3887,C3585:C3849)),NA())

Is there a way to switch the NA for a blank cell or something average function would not pick up on

Thank you

 

[ExcelDJR]

Figured it out "" instead of NA

 

[FDibbins]

Try changing your formula to this...
=AVERAGEIF(Q3585:Q3849,A3887,C3585:C3849)