Median Blanks Array

    Hello all. I am using an array formula to calculate a median. This works except that bizarrely it counts blanks as values and when charted displays them as zero.

    Quote

    =MEDIAN(IF(INDIRECT(IJ13)=INDIRECT("Analysis!"&IJ14),INDIRECT(IT28)))


    So I am trying to add another criteria to the formula. I've tried a few variations, including the one below, but all return a #VALUE error.

    Quote

    =MEDIAN(IF(INDIRECT(IJ13)=INDIRECT("Analysis!"&IJ14)*(ISNUMBER(INDIRECT(IT28))),INDIRECT(IT28)))


    When I stepped through the formula evaluation it seemed to evaluate all the blanks as zero. So the problem may be with the data but I can't think what.


    Does anyone have any suggestions and/or workarounds?


    TIA.

    Re: Median Blanks Array


    Just checked, zeros are displayed so they are blanks. Oddly when I select a cell and press delete the egg timer appears for a split second as if it's having to think about something - but I guess that could just be recalculating formulae etc.

    Re: Median Blanks Array


    Without going through and extracting all the relevant "Unitary" values, it looks as if the formula you have now is doing what it should. Certainly in that formula, blanks seem to be treated as blanks.


    Now for the new formula, the cell IT28 is not what you mean - what should that be? But firstly, you didn't mention that these are array formulas; are you confirming with Ctrl + Shift + Enter?

    Re: Median Blanks Array


    Is MEDIAN really what you want? All you want to know is which is the middle number in a range of numbers (remembering that if there is no true middle number i.e. the group is even, then it takes he two middle ones and averages them)?


    I just don't see how this is of any use to you as all it tells you is the number assigned to the hospital which is in the middle of your selected list.


    Or am I reading it completely wrong?!?


    KJ

    Re: Median Blanks Array


    Quote

    Without going through and extracting all the relevant "Unitary" values, it looks as if the formula you have now is doing what it should. Certainly in that formula, blanks seem to be treated as blanks.


    I've extracted the data in the attachment. Can anyone explain where the value of 18.75 in E5 comes from?

    Quote

    Now for the new formula, the cell IT28 is not what you mean - what should that be?


    To get the file down to manageable size to attach I removed a lot and the references changed so the original formulae I quoted have changed.

    Quote

    But firstly, you didn't mention that these are array formulas; are you confirming with Ctrl + Shift + Enter?


    Yes!

    Quote

    Is MEDIAN really what you want? All you want to know is which is the middle number in a range of numbers.


    That is the median isn't it? Or are you onto something I've missed? This is doing my head in...

    Re: Median Blanks Array


    Isn't 18.75 the right answer?
    If you extract the unitary items, sort them in order there are 54 items
    Item 27 = 17.5, item 28 = 20
    Average = 18.75 ( 18.8)
    Isn't this the median?

    "The light that burns twice as bright burns for half as long "

    Re: Median Blanks Array


    Sorry, I should have put a question mark at the end of that second sentence as well. :confused:


    The Median is the value of the number in the middle of a list of numbers e.g.


    1, 400, 3, 100, 200, 1000, 2000 the median would be 100 as it is 4th number in the list of 7 (3 numbers before it, 3 after; regardless of their value)


    With your list, the Median (if i've worked it out correctly) would be 18.75. Is that really of any use to you?!?


    Edit: Typing error.


    KJ

    Re: Median Blanks Array


    Think I may have missed your point.
    You want the array formula to treat the empty cells properly i.e ignore them?
    As a workaround you could put spaces in or type "Blank"

    "The light that burns twice as bright burns for half as long "

    Re: Median Blanks Array


    Confession time - I'm as baffled as you are - Excel should exclude empty cells, blanks & text from a MEDIAN calc for arrays.

    "The light that burns twice as bright burns for half as long "

    Re: Median Blanks Array


    Believe it or not, I think the problem with this formula:

    Quote

    =MEDIAN(IF(INDIRECT(IJ13)=INDIRECT("Analysis!"&IJ14)*(ISNUMBER(INDIRECT(IT28))),INDIRECT(IT28)))


    is merely one of brackets, as below seems to work.

    Quote

    =MEDIAN(IF((INDIRECT(IJ13)=INDIRECT("Analysis!"&IJ14))*(ISNUMBER(INDIRECT(IT28))),INDIRECT(IT28)))


    So that gets round mysterious problem of blanks.


    My related problem is that I can't get blanks not to be displayed in a graph. Data labels are showing and it shows as zero if the cell has blank, '-' or 'n/a'.


    This may be a separate question, not sure. Any thoughts on that welcome, and thanks to those who've bent their minds above.

    Re: Median Blanks Array


    Well in the Book4 example I was able to get the right result by adding an AND statement, such as


    {=MEDIAN(IF(AND(A169:A222="Unitary", B169:B222<>" "),B169:B222))}


    This gave the Median as 100, which I believe is right.


    Don't know whether you can adapt that at all in your formula?


    KJ

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login