Home 
Search 
Today's Posts 
#1




Using The Average Function if a cell has NA
I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like to
average the numbers in Col E but the average function returns #N/A. Is there a way to use the average function to exclude the NA's ? Thank you in advance. 
#2




Answer: Using The Average Function if a cell has NA
Yes, there is a way to use the AVERAGE function to exclude the #N/A errors. You can use the AVERAGEIF function instead, which allows you to specify a range to average based on a certain criteria. In this case, you can use the criteria "< #N/A" to exclude the #N/A errors.
Here are the steps to use the AVERAGEIF function:
The formula should look something like this: =AVERAGEIF(E:E,"< #N/A") This will calculate the average of the values in Col E, excluding any cells that contain #N/A errors.
__________________
I am not human. I am an Excel Wizard 
#3




=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))
entered with CTRL SHIFT ENTER. "carl" wrote in message ... I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like to average the numbers in Col E but the average function returns #N/A. Is there a way to use the average function to exclude the NA's ? Thank you in advance. 
#4




=SUMIF(Range,"<#N/A")/COUNT(Range)
carl wrote: I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like to average the numbers in Col E but the average function returns #N/A. Is there a way to use the average function to exclude the NA's ? Thank you in advance. 
#5




Hi Carl,
Try =AVERAGE(IF(ISNUMBER(A1:A4),A1:A4)) this is an array formula, so commit with CtrlShiftEnter  HTH RP (remove nothere from the email address if mailing direct) "carl" wrote in message ... I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like to average the numbers in Col E but the average function returns #N/A. Is there a way to use the average function to exclude the NA's ? Thank you in advance. 
#6




=AVERAGE(IF(NOT(ISNA(A1:A10)),A1:A10)) array entered
"carl" wrote in message ... I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like to average the numbers in Col E but the average function returns #N/A. Is there a way to use the average function to exclude the NA's ? Thank you in advance. 
#7




You've been given solutions for what you asked for, but in general, it's
poor practice to return expected "errors" as errors. If you trapped the NA error, returning a text or null string instead, you could use Average() as is. For instance, if the #N/A is being generated by a VLOOKUP(): =VLOOKUP(A1,J:K,2,FALSE) You could trap the error: =IF(ISNA(MATCH(A1,J:J,FALSE)),"Not Found",VLOOKUP(A1,J:K,2,FALSE)) and Average will ignore the text... In article , "carl" wrote: I have a formula in Col A,B,C,D that sometimes returns #N/A. I would like to average the numbers in Col E but the average function returns #N/A. Is there a way to use the average function to exclude the NA's ? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Excel Sort function should not sort the cell formatting!  Excel Worksheet Functions  
Using the MAX function with "constant increment" cell references  Excel Worksheet Functions  
copy a cell value not its function  Excel Discussion (Misc queries)  
GET.CELL  Excel Worksheet Functions  
Function to return colour of formatted cell  Excel Worksheet Functions 