Excel Average Need to ignore #n/a values

mflagstad7
mflagstad7 used Ask the Experts™
on
Hi,

I have a spreadsheet with one column of data based on a VLOOKUP from another sheet. Meaning, I select a value from a drop down list and it automatically populates a currency value in the next column depending on the selection. For the rows that do not have a value selected, the currency value in the next column just displays #N/A (not very professional, but doesn't really bother me). In a third field, I want to show the average of all the currency. When I add the formula, =AVERAGE(J1:J20) - I get an error because only J1:J5 has currency values; all the rest are error messages. I saw another post in here talking about a similar thing - and the assisted answer was that the average formula would ignore non numeric values - however, i've found this to be untrue. Any help would be greatly appreciated.

Thanks!
Michelle
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013
Commented:
AVERAGE function ignores text and logical value (TRUE/FALSE) but not errors
You can use an array formula like this
=AVERAGE(IF(ISNUMBER(J1:J20),J1:J20))
which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar
Perhaps simpler, if you have no negative values
=SUMIF(J1:J20,">0")/MAX(1,COUNT(J1:J20))
regrads, barry
TracyVBA Developer

Commented:
To get rid of the NA in your vlookup try this:

=IF(ISNA(Vlookup(A1,myRange,1,false),"",Vlookup(A1,myRange,1,false))

This way your average will work, because there's no text values.
Most Valuable Expert 2013

Commented:
Here's an example of both formulas I suggested above. If you have any negative values then the second one can be adjusted like this to accommodate
=SUMIF(J1:J20,"<9.9E+307")/MAX(1,COUNT(J1:J20))
regards, barry

25103242.xls
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Jorge PaulinoIT Pro/Developer
Top Expert 2008

Commented:
You can use also (for A1 to A10)
=AVERAGE(IF(ISNA(A1:A10);0;A1:A10))
(with CTRL+SHIFT+ENTER)
Most Valuable Expert 2013

Commented:
Hello jpaulino,
That formula doesn't ignore #N/A values, it treats them as zero, so if your range has 8 #N/As and the value 1 and 5 then it'll give an average of 0.6, i.e. the sum of the range divide by 10, I assuming the required answer is 3, i.e. just averaging the numbers.
To do that with your version you'd need to return a blank (or some text) when the IF is TRUE, i.e.
=AVERAGE(IF(ISNA(A1:A10);"";A1:A10))
regards, barry
Jorge PaulinoIT Pro/Developer
Top Expert 2008

Commented:
Opps! Sorry ... my bad!
Thanks for the correction Barry :)
Most Valuable Expert 2013

Commented:
No problem.....
One more thing to add. You don't say what version of excel you are using. The above formulas should all work in any version but if you have Excel 2007 you could aslo use AVERAGEIF function, i.e.
=AVERAGEIF(J1:J20,"<>#N/A")
barry

Author

Commented:
thank you! so fast everyone!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial