Need a formula to ignore #N/A, but use zeros

[stacyrae7]

I have a spreadsheet where I am trying to write a formula to calculate an average. The columns being used may be blank or #N/A, which I want to ignore in the formula. Or it may contain a zero or a number 1 - 5 that I want to be used.

A1 A2 A3 A4 A5 A6
0 1 #N/A 3 5 blank

I'm looking for the average

 

[T. Valko]

I have a spreadsheet where I am trying to write a formula to calculate an average. The columns being used may be blank or #N/A, which I want to ignore in the formula. Or it may contain a zero or a number 1 - 5 that I want to be used.

A1 A2 A3 A4 A5 A6
0 1 #N/A 3 5 blank

I'm looking for the average

Try this array formula**.

=IF(COUNT(A1:A6),AVERAGE(IF(ISNUMBER(A1:A6),A1:A6)),"")

** 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.

 

[MrKowz]

Welcome to the forums!

Ideally, you want to get rid of the errors at the source rather than trying to compensate for them in formulas further down the line. This way, you don't have to create an array-entered formula that checks for errors each time.

The easiest way to do this is for the cells where you have the formulas that can error, use:

Excel 2003 or older:
=IF(ISERROR(*your formula*),"",*your formula*)

Excel 2007 or newer:
=IFERROR(*your formula*,"")

 

[stacyrae7]

Try this array formula**.

=IF(COUNT(A1:A6),AVERAGE(IF(ISNUMBER(A1:A6),A1:A6)),"")

** 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.

THANK YOU, THANK YOU, THANK YOU, T. VALKO! This formula did exactly what I was looking for!

 

[DonkeyOte]

FWIW, another alternative:

=IF(COUNT(A1:A6),SUMIF(A1:A6,">=-9.99E+307")/COUNT(A1:A6),"")

 

[T. Valko]

THANK YOU, THANK YOU, THANK YOU, T. VALKO! This formula did exactly what I was looking for!

You're welcome. Thanks for the feedback!