Clarify the way MEDIAN works

[DivideByZero]

Hi I wish to calculate the median of a column of numbers where the number is greater than zero. Sometimes there will be text in the column and I want those to be ignored.

Reading the help documentation for Excel 2007 it states: "Arguments that are error values or text that cannot be translated into numbers cause errors". Now I was expecting that if for example I had "Text" in the column that I would get an error in the median. However I do not, "Text" is ignored and the median of the numbers are calculated correctly.

What is going on? Am I interpreting the Excel documentation incorrectly or is the documentation incorrect? Could this change with future releases?

To be on the safe side should I use something like:

=MEDIAN(IF(IF(ISNUMBER(J15:J17),J15:J17>0),J15:J17))

and enter the formula as an array?

Thank you for reading this.

 

[pgc01]

Hi
Welcome to the board

I think you are making a confusion between these 2 points in the help:

• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.

If you try:

=MEDIAN(1,"a",2)

one of the parameters is a text value and so you'll get an error, as stated in the second line of the help that I posted.

If you try:

=MEDIAN(A1:A10)

you have 1 parameter that is an array. If, for ex., all the values in A1:A9 are numbers and A10 is text, A10 is ignored.
Notice that the parameter of MEDIAN is an array, not a text value, and so it follows the first line of help that I posted.

 

[DivideByZero]

Thank you PGC01.

Your response is most helpful and certainly clarified Median() for me.

Dea ghuí

 

[pgc01]

I'm glad it helped. Thanks for the feedback.