Getting MEDIAN and PERCENTILE to exclude #ERROR values

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.

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!

In article <UTqfg.2573$hv1.1679@trnddc01>,
"THOMAS CONLON" <[email protected]> wrote:

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

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.

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
> In article <UTqfg.2573$hv1.1679@trnddc01>,
> "THOMAS CONLON" <[email protected]> wrote:
>
>> 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.