How to overlook zeros in a calculation?
 

I am calcing a median value for a range, and the range includes zeros. I've
tried an array formula: ={if(A1:A40,median(A1:A4),0)} and another version of
the same: ={Median(if(A1:A40,A1:A4,0))}

Both of these formulas include the 0 values in the median calculation.

Does anyone know how I can remove them?

Thanks!

How to overlook zeros in a calculation?
 

=MEDIAN(IF(A1:A40,A1:A4))
as an array formula
--
Gary''s Student - gsnu200755

How to overlook zeros in a calculation?
 

Hello Gary,

I've tried that exact Array formula and for some reason it still picks up
the zeros.

"Gary''s Student" wrote:

=MEDIAN(IF(A1:A40,A1:A4))
as an array formula
--
Gary''s Student - gsnu200755

How to overlook zeros in a calculation?
 

Thanks Gary, I tried it again and it worked. :)

"Gary''s Student" wrote:

=MEDIAN(IF(A1:A40,A1:A4))
as an array formula
--
Gary''s Student - gsnu200755

How to overlook zeros in a calculation?
 

Median ignores text so try:

={Median(if(A1:A40,A1:A4,""))}

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"pa1971" wrote in message
...
I am calcing a median value for a range, and the range includes zeros.
I've
tried an array formula: ={if(A1:A40,median(A1:A4),0)} and another version
of
the same: ={Median(if(A1:A40,A1:A4,0))}

Both of these formulas include the 0 values in the median calculation.

Does anyone know how I can remove them?

Thanks!