Average Formula and #NA Error

[danielstone121]

Hey Excel Gurus-
Hopefully this is an easy one for you. I just need a simple average formula but my data contains numeric values, zeros (that I need calculated into the average) and #NAs that I DON'T want calculated. Is there an average formula that will add up all numeric values including zeros but that will ignore all of the #NAs?

 

[Jonmo1]

You might be better off eliminating the #N/A's at the source.
Can you post the formula that is producing the #N/A ? it can be adjusted to return a blank "" or whatever else you want.

Which version of Excel do you have?

 

[schielrn]

Try something like:

=average(if(isnumber(a1:a100),a1:a100))

This needs to be confirmed with control+shift+enter and not just enter.

Hope that helps.

 

[shyy]

Look into IFERROR function or ISNA

 

[danielstone121]

Thank you for your help everyone! Okay, it is Excel 2010, the numbers I am averaging are percentages, and the formula that is producing the #NA error is: =VLOOKUP(A:A,'Test Data'!A:AO,41,FALSE). Does that help? I tried the if is number configuration and all I got back is zero. I'm guessing it wasn't counting the values because they are percentages. =(

 

[Jonmo1]

You could try

=AVERAGEIF(B1:B100,">=0")

Assuming there are no negative values.


Or even
=AVERAGEIF(B1:B100,"<>#N/A")

 

[danielstone121]

Thank you everyone! The =AVERAGEIF(B1:B100,"<>#N/A") worked perfectly! Thanks to all, but especially MrExcel MVP. Have a great day guys!