How to overlook zeros in a calculation?

[Guest]

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

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

Does anyone know how I can remove them?

Thanks!

 

[Guest]

=MEDIAN(IF(A1:A4>0,A1:A4))
as an array formula

 

[Guest]

Hello Gary,

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

 

[Guest]

Thanks Gary, I tried it again and it worked.

 

[Sandy Mann]

Median ignores text so try:

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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk