Quartile/Average/Median In Array With Missing Data Points

Guys, I am absolutely desperate here...
The situation is the following: I need to use an Quartile formula for an array of numbers, meaning 2 data sets basically. First data set is a Group for each point there IS a value there (e.g. group ID) and nothing is missing. For the second (matching) data sets some points might be missing and in this case a lot of them are.
The problem: when I use an array formula on those data sets, it shows 0 for quartiles and median. And it shows "wrong" (low) value for average.

However, when I copy/paste the same data set separately and take quartiles/median/average and it DOES show values and not zero. So is there a way to make an array formula work (display results) the same way it works when I apply it to a data set separately?


The thing is that I absolutely MUST find a way to use it as an array, because my data structured in such a way (2 columns, 1 with 4 types of group id, and 1 with matching values - and I need to see quartiles/etc for them) and I can't change it much. I mean, I do understand that probably due to the fact that I'm using an array excel expects every matching point from data set 1 to have a matching entry in data set 2, but it's just not the case here. And I'm literally killing myself trying to find a way for array to "ignore" empty entries and work with what's available.

I would be forever thankful for any help on this, because I really am desperate here ((



P.S. You can find a sample file attached.

Try adding an extra IF to check that the second range isn't blank, e.g. for Quartile 1 try

=QUARTILE(IF(A2:A543=1,IF(B2:B543<>"",B2:B543)),1)

and simialr for MEDIAN

For AVERAGE you can use AVERAGEIF which will ignore blanks and dosen't need "array entry", i.e.

=AVERAGEIF(A2:A543,1,B2:B543)

Originally Posted by daddylonglegs

Try adding an extra IF to check that the second range isn't blank, e.g. for Quartile 1 try

=QUARTILE(IF(A2:A543=1,IF(B2:B543<>"",B2:B543)),1)

and simialr for MEDIAN

For AVERAGE you can use AVERAGEIF which will ignore blanks and dosen't need "array entry", i.e.

=AVERAGEIF(A2:A543,1,B2:B543)

Thank you so, so much! You're truly a life-savior! : )))

Now, another issue related to that: in I have a number of columns with sum (so a simple =SUM() formula there), but the problem is that many of those sums return zeros as result (meaning that in those cells are formulas, not static values!). And that (those formulas returning zeros) makes quartile and median formulas either show nothing (0) or wrong result.

Is it possible to make the above array formula ("=QUARTILE(IF(A2:A543=1,IF(B2:B543<>"",B2:B543)),1)") to also ignore zeros (as well as blanks)? And the thing is that unfortunately it also MUST incorporate both conditions into one formula, due to data structure (meaning columns with blanks go first, then a sum column, then blanks again, sum again, etc.).


Thank you so much in advance!



P.S. I did try this: "=QUARTILE(IF(A2:A543=1,IF(OR(B2:B543<>"",B2:B543>0),B2:B543)),1)" - doesn't seem to be working (returns a zero like it was with above-suggested array formula) :/ On the other hand, "=QUARTILE(IF(A2:A543=1,IF(B2:B543>0,B2:B543)),1)" works - so >0 option shows a correct result, but not both conditions in one formula

Yes, I think that latter one should work for you, i.e.

=QUARTILE(IF(A2:A543=1,IF(B2:B543>0,B2:B543)),1)

blank cells are actually treated as zeroes so that will ignore both, for average if you want to ignore zeroes you can use AVERAGEIFS, i.e.

=AVERAGEIFS(B2:B543,B2:B543,">0",A2:A543,1)

Originally Posted by daddylonglegs

Yes, I think that latter one should work for you, i.e.

=QUARTILE(IF(A2:A543=1,IF(B2:B543>0,B2:B543)),1)

blank cells are actually treated as zeroes so that will ignore both, for average if you want to ignore zeroes you can use AVERAGEIFS, i.e.

=AVERAGEIFS(B2:B543,B2:B543,">0",A2:A543,1)

Thank you so much for your kind reply! : )))
However, that's the thing: I need to come up with a formula where BOTH conditions would be incorporated into the SAME (one) formula. Meaning I need to have both "B2:B543>0" and "B2:B543<>0" conditions there. I tried using both of them with OR() construction, but it doesn't seem to work [it works as if only "B2:B543<>0" is applied]
The reason (for the need to have both in one formula), as I mentioned above is that this formula is applied to two types of columns: one where there are no formulas, no zeros (blanks alone): only values/numbers there. And the other one, where there's a simple SUM() used (sum of type 1 column values), many of which return a zero (adding up empty cells). So when the formula "sees" the first type, it should apply "B2:B543<>0" and thus return a correct result, and when it "sees" the second type it should apply "B2:B543>0" and thus return a correct result.

Thanks so much in advance again!

Generally speaking you can't use OR within array formulas as you have tried because OR returns a single value (TRUE or FALSE) rather than an array of values as you want here. You can get the same result with + so, for example

=QUARTILE(IF(A2:A543=1,IF((B2:B543>0)+(B2:B543<>""),B2:B543)),1)

.....but I can't really see how you would need that. TRUE blanks will be interpreted as zeroes so you can exclude both blanks and zeroes by using >0 (I assume there are no negative values). If you have any "formula blanks" ("" returned by a formula) then those would also be ignored, so I expect that previous formula would give you the same result as the one in this post....

hello
did you tried hitting ctrl + shift + enter instead of enter only... by the way i never used quartile

Originally Posted by vlady

hello
did you tried hitting ctrl + shift + enter instead of enter only... by the way i never used quartile

I did. The formula we're discussing here simply wouldn't work at all (produces #VALUE!) without an array involved.
But thanks so much for checking: better safe than sorry!

I feel I might be missing something - is it possible to post an example workbook showing where the problem lies and the results you want?

Originally Posted by daddylonglegs

I feel I might be missing something - is it possible to post an example workbook showing where the problem lies and the results you want?

Well, something might as well be
Please find a sample workbook here.

Have a look at the entries marked red and compare them with those to the left: e.g. G9 with F9, G10 with F10, etc. As I figured out, the reason are the zero values returned by some formulas in column F (scroll down, I marked them as well). So as soon as I "fixed" those zeroes by adjusting a formula in column G, it worked fine.

Also, there are other puzzling aspects, which I simply can't figure out: have a look at D11 and E11. It shows zeros there. But if I apply a filter of 3 to PG column (B37; since formula needs to be group-specific [array basically]), then copy resulting filtered values in either E or D columns to a separate sheet, and apply a quartile formula, it DOES show values there. So basically when I do it manually, without an array, it works and otherwise it does not. How can this be?


I'd very much appreciate any help on the matter, since for the love of god I can't figure this out..