# 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

Are they really blanks? Make sure you have Zero values ticked in Tools, Options, Window options.

• 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

Can you attach a workbook showing the problem, please? It's not easy without knowing what is in the indirect references.

• Re: Median Blanks Array

BTC,
File attached. I've slimmed down considerably but tried to retain as much of the original structure as possible. It may have something to do with the Indirect formula but apart from anything else I don't understand how it's arriving at a median of 18.8 for the current example. I'm flummoxed!
Stephen

## Files

• 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...

## Files

• Re: Median Blanks Array

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

P1g5purt - yes, you're right. I did say it was doing my head in. Thanks for clearing that up but the question remains: why is it including blanks in the calculation?

• 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

Quote from P1g5purt

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

It is not taking any notice of the blanks.

• 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!