Getting MEDIAN and PERCENTILE to exclude #ERROR values

T

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.
 
D

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!
 
T

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.
 
Top