#N/A

[John V.]

When you enter a function such as median or average which
refers to a range of cells that have one or two #N/A's, it
does not return a value but returns #N/A. What I would
like is to have the function simply exclude NA's and
calculate the function based upon the data that is
available without having to continously paste and sort the
data in a different area. Is there some way to do this?

 

[Peo Sjoblom]

You can overcome that by using an array formula

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

entered with ctrl + shift & enter

or

=SUMIF(A1:A10,"<>#N/A",A1:A10)/COUNTIF(A1:A10,"<>#N/A")