Getting MEDIAN and PERCENTILE to exclude #ERROR values

[THOMAS CONLON]

Is there a way to get PERCENTILE or MEDIAN to exclude error values? I give
as an argument a range in which some of the values are #VALUE, but rest are
valid, legal numeric values, and it is those remaining non-error values that
i want MEDIAN or PERCENTILE of. Thank you.

 

[Domenic]

Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=MEDIAN(IF(ISNUMBER(A2:A100),A2:A100))

and

=PERCENTILE(IF(ISNUMBER(A2:A100),A2:A100),0.8)

Adjust the references and percentile value accordingly.

Hope this helps!

 

[THOMAS CONLON]

This is really wonderful, excellent information! I've run into problems
like this for a while and always used inconvienent kludges. Glad i asked!
Thank you! Tom.