Thread: Median

Hi, hope you can help me with a questions.



I have five number in five cells (in EXCEL). I have to calculate the median of them like 3,5,6,8 and 10 and the result of this should be 6, right.

Those numbers are results of other equation anda some times the value of one or two of them is #DIV/O or 0.

When the value is 0 I need to ignore 0 as a number like: 3,5,0,0,10. Because the result of thisp should be 5 without considering the 0. But with the 0 the result is 3.

I need the function MED ignore the #DIV/0 or ignore the 0 value.

Is that possible? Thank you much for your atention.

Welcome to the forum.

Zero is most definitely a number. Make those cells blank if you want them ignored.

You could change the the equation can return a null string instead of a 0.

Or use

=median(if(a1:a5<>0, a1:a5))

... confirmed with Ctrl+Shift+Enter.

Hi, shg, thank for you help.


The equation before that give me the result that are entered in the MED column is:


=U55(T55*6.3)*10

How this equation can give me null sign? Or zero sign?

After that, if it give me null sign the function MED will work correctly i suppose, right?

And if it give me Zero sign, how can MED function ignore Zero numbers?


If one of this works it´s solve the problem. Thanks again.

=if(t55*u55=0, "", t55*u55*6.3*10)