Calculating median and average within an amount range and excluding zero.

[elsmith9035768]

There are 10 cells that will contain numbers, but some will be blank or have a zero. I need to auto calculate the median and average within a high low range and exclude any cells that have a zero in the cell.

My questions are in P61, P62, P64, P65, and P68.

Thanks in advance.

 

[GraH - Guido]

Perhaps
[P56]=MEDIAN(IF(P4654<500,P4654))
[P57]=AVERAGEIFS(P4654,P4654,"<"&500) or =AVERAGEIF(P4654,"<"&500,P4654)

[R56]=MEDIAN(IF(R46:R54>0,R46:R54))
[R57]=AVERAGEIFS(R46:R54,R46:R54,">"&0) or =AVERAGEIF(R46:R54,">"&0,R46:R54)

Median formula to confirm with control+shift+enter (array formula)

 

[elsmith9035768]

Good deal. Worked like a charm. Thanks.

 

[GraH - Guido]

Those smileys were unintended to be clear