ERROR IN MEDIAN MULTIPLE IF CRITERIA WHEN ZERO INCUDED IN RANGE CRITERIA

[surbannomad]

I am trying to calculate a median for a range of numbers that include zeros

=MEDIAN(IF(AP:AP>=0,IF(AP:AP<=5, AP:AP)))

when it calculates it returns an answer of zero when it should actually be 1.....how do I get it to work correctly have double checked, when I change start value to 1 it produces correct anser but not when using zero as start value...help pls

 

[joeu2004]

when it calculates it returns an answer of zero when it should actually be 1

And we should just take your word for it?

Provide a small example of data that demonstrates both the wrong result with AP:AP>=0 and the correct result with AP:AP>=1.

Also provide the two formulas (correct and wrong results).

Be sure to include the cell names for the data and formulas. Ideally, use XL2BB to present both.

Did you array-enter the formulas? That is, press ctrl+shift+Enter instead of just Enter so that the formulas are surrounded by curly braces in the Formula Bar, to wit:
{ =MEDIAN(IF(AP:AP>=0,IF(AP:AP<=5, AP:AP))) } ?

PS.... Using AP:AP is a bad idea. It causes Excel to create temp arrays of 1+ million elements. Unless you truly need to allow for 1+ million data, it is better to use a reasonable limited range, e.g. $AP$1:$AP$10000. Even smaller, if "reasonable".

 

[surbannomad]

Hi Thanks for responding cant install computer but have included pic with formual in quotes as you can see there is something not quite right and I am at a loss to work out why, 1.5 is correct using straight median on limited column, second one should be 1.5...so now thats wrong and finally third it should be 3.5 but only showing 3. I am missing something so hope you can enlighten..where I am going wrong

 

[joeu2004]

In the case of { =MEDIAN(IF(A3:A12>=0,IF(A3:A12<=5,A3:A12))) }, note that =MEDIAN(A3:A11) also returns 1. So the result of the array-entered formula should be expected.

And in fact, it is correct. The median is a value such that the same number of values is below and above.

A3:A11 comprise 9 values, and 1 is the middle (4 values below and above), to wit: 0 0 0 0 1 2 3 4 5 .

In the case of { =MEDIAN(IF(A3:A12>=1,IF(A3:A12<=5,A3:A12))) }, note that =MEDIAN(A7:A11) also returns 3. So the result of the array-entered formula should be expected.

And in fact, it is correct. A7:A11 comprise 5 values, and 3 is in the middle (2 values below and above), to wit: 1 2 3 4 5 .

 

[surbannomad]

Thanks you so much you are correct and I can see your logic just realised my error...I am tired and I was applying a sort and so I was looking at the wrong list of number sorted instead of unsorted...fatigue does strange thing to the mind.