Understanding array requirements: some formulas need ctrl+shift+enter, while others don't?

[charleyboy81]

Dear MrExcel-ers,

Thanks to this forum, I discovered how to Min-IF without arrays, see formula A below.
I tried to apply this method to create Median-IF without arrays, see formula B below.

So, two questions:
1) Can someone enlighten me why A works and B doesn't?
2) Is Median-IF possible without using arrays?

Note, when I say "arrays", I mean ctrl+shift+enter formulas.

Formula A: =LARGE(INDEX(([CriteriaRange]=[Criteria])*[ValueRange],0),COUNTIF([CriteriaRange],[Criteria]))
Formula B: =MEDIAN(INDEX(IF([CriteriaRange]=[Criteria],[ValueRange],""),0))

Thanks for your help, Charley.

 

[pgc01]

Hi Charley
Welcome to the board

First a remark: Your formula for the Min-IF is only to be trusted if you have no zero or negative values.

1 - The formula A works because it was modified to work.

The usual Min-If formula is simply

=LARGE(([CriteriaRange]=[Criteria])*[ValueRange],COUNTIF([CriteriaRange],[Criteria]))

It requires CSE. We can add the INDEX(,0) to the values to get the array of values of the product. That's what you did in your formula and that's why it's working without the CSE.

The formula B does not work because it has an IF, and in the case of an IF there's no workaround to avoid CSE. The INDEX(,0) will not work as a workaround to avoid the CSE in case of an IF.


Notice that this also means that you can remove the INDEX, that is doing nothing there, and simplify the formula:

=MEDIAN(IF([CriteriaRange]=[Criteria],[ValueRange])


2 - This is a Min-If simpler (and more reliable) formula:

=AGGREGATE(15;6;[ValueRange]/([CriteriaRange]=[Criteria]);1)

You can use the same principle to the Median, that is the value in the middle.

 

[charleyboy81]

Thank you PGC. I'll investigate using AGGREGATE in this way.

I had hoped that INDEX(,0) would apply more generally. It seems odd that it doesn't, but for now I shall simply accept the rule!

Kind regards, Charley.

 

[pgc01]

I hope it helped.

You can use the same principle to the Median, that is the value in the middle.

Sorry, you know this, of course, but just to be precise, the median is indeed the value in the middle if you have an odd number of values, but if you have an even number of values, the median is the average of the 2 in the middle.

 

[charleyboy81]

...You can use the same principle to the Median...

I experimented using AGGREGATE to do Median-IFs: it worked when I just reference a range of cells containing numbers & errors, but when using a reference of [ValueRange]/([CriteriaRange]=[Criteria] it didn't seem to. Have you definitely succeeded doing this... any idea on errors I might be making?

Weirdly, in the case when it was working, if I evaluate the range first and then the whole formula it didn't work either.

Thank you once again!

 

[pgc01]

Hi

From what you posted I guess you are trying to use directly the Median (function 12) from Aggregate() which does not work, as it will only accept a range.

What you have to do is to calculate the median from its definition, like I said in my previous posts.

As I wrote before, assuming a set of values sorted (ascending or descending order, doesn't matter) lthe median is
- the value in the middle if the number of values in odd
- the average of the 2 values in the middle if the number of values is even

The Aggregate() Median function does not work with arrays, but the Small function (15) works, as you already now from the formula you used for the Min-If. So does the Large function (14).

This means you can use either to calculate the median

For ex., let's assume

- Names in A2:A15
- Values in B2:B5
- Some error values in A2:B15

To calculate the Median for the name "ABC", I'd try something like (not tested, don't have excel until Monday)

=SUM(AGGREGATE(15,6,B2:B15/(A2:A15="ABC"),ROUNDUP(COUNTIFS(A2:A15,D2,B2:B15,"<1e300")/2+{0.1,-0.1},0))))/2

Notice that since we are getting the value from the middle, it does not matter if we use Large (14) or Small (15).
Notice also that the values do not have to be sorted.

Hope it helps, or I'll post a tested formula on Monday.

 

[charleyboy81]

This is all making sense to me now - thanks again for your help, and your thorough answers.

With kind regards, Charley.

 

[pgc01]

You're welcome. Thanks for the feedback.