How to overlook zeros in a calculation?

P

pa1971

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

pa1971

Hello Gary,

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

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top