average IF with errors + 0's

[Kanuck]

Hello,

Trying to solve why my formula to average with errors is not adding up to the same number as it would when I use the formula that doesn't account for errors.
What I had that I need to change (because my table will have errors): =AVERAGEIF(A1:A30, "<>0")

What I am trying, but it accounts for the 0s, so doesn't return the average I'm looking for: {=AVERAGE(IF(ISNUMBER(A1:A30),A5:A30,"<>0"))}

Thank you for help in advance!
Kanuck

[Phuocam]

Try this ...

=AVERAGE(IF(ISNUMBER(A1:A30)*(A1:A30<>0),A1:A30,""))

Enter with Ctrl+Shift+Enter.

[Tony Valko]

Try this array formula**:

=AVERAGE(IF(ISNUMBER(A1:A30),IF(A1:A30<>0,A1:A30)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.