character that is ignored when averaging

[Guillermo]

Hello:
I have several cells to average.
In some of those cells, I have invalid values, so I have an if statement
that replaces the cells with #N/A is the values are invalid.
This is good because it allows me to graph the values and the #N/A are
ignored (in an X-Y scatter plot)
However, if I try to average and the range includes one of those cells, I
get a value of #N/A. I want to be able to average those values ignoring the
#N/A. If I replace the #N/A with '' or something similar, then the average
works but the graph doesn't (it gets turned into a line plot).
If there something tat satisfies both requirements?

thanks

guillermo

 

[Jason Morin]

One way:

=SUMIF(A:A,"<>#N/A")/COUNT(A:A)

HTH
Jason
Atlanta, GA

 

[Guillermo]

thanks

guillermo

One way:

=SUMIF(A:A,"<>#N/A")/COUNT(A:A)

HTH
Jason
Atlanta, GA