Excel Average Need to ignore #n/a values
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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")
regards, barry
25103242.xls
=SUMIF(J1:J20,"<9.9E+307")
regards, barry
25103242.xls
You can use also (for A1 to A10)
=AVERAGE(IF(ISNA(A1:A10);0
(with CTRL+SHIFT+ENTER)
=AVERAGE(IF(ISNA(A1:A10);0
(with CTRL+SHIFT+ENTER)
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);"
regards, barry
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);"
regards, barry
Opps! Sorry ... my bad!
Thanks for the correction Barry :)
Thanks for the correction Barry :)
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
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
ASKER
thank you! so fast everyone!
=IF(ISNA(Vlookup(A1,myRang
This way your average will work, because there's no text values.