Using mathmatical functions on ranges that have #NA

[mike]

I need to take the median of a column of data, but some
of the cells in my intended range have the #NA or #Value
error. When you try to use the functions with data cells
that have error values, the result is an error value. I
can't filter the rows of data because I need to have
visibility to the information in other columns.

median(a1:a5) = #NA

A1 5
A2 6
A3 7
A4 8
A5 #NA

Thanks

I

 

[Paul Corrado]

Mike,

=Median(IF(ISNUMBER(A1:A5)=TRUE,A1:A5))
array entered (ctrl+shift+enter)

Formula will appear in "{ }"

HTH

PC

 

[Thomas]

Use =if(isna,formula),"",formula) in the cells such as A5 that causing
your other formula to error.