Excel Aggregate Function If More Than One Criterion 2

[PWD]

Good morning, I have just found some formulae to enable me to find the MEDIAN (and 95th Percentile) using one criterion on another column:

=AGGREGATE(16,6,(K4:K12)/((LEFT(C4:C12,2)="LD")*(K4:K12<>"")),0.5)

=AGGREGATE(16,6,(K4:K12)/((C4:C12="RAT")*(K4:K12<>"")),0.5)

What I would like to be able to do is do an 'OR' on Column C to get the Median of Column K if it's either LD* or RAT in Column C.

I have tried:

=AGGREGATE(16,6,(K4:K12)/((Or(LEFT(C4:C12,2)="LD",C4:C12=”RAT”))*(K4:K12<>"")),0.5)

But this just gives a #NUM! error.

Am I trying the impossible or have I just got incorrect sytax?

Many thanks,
D€$

 

[Deniall]

I've only got a few seconds as it's long past my bedtime, but the way you have AND'ed your conditions reminds me of the way one can coerce SUMPRODUCT into AND'ing.[&nbsp;] Therefore perhaps the way one can coerce SUMPRODUCT into OR'ing might work for you.

Try changing
Or(LEFT(C4:C12,2)="LD",C4:C12=”RAT”)
to
SIGN((LEFT(C4:C12,2)="LD")+(C4:C12=”RAT”))
and see how you go.

No guarantees, not even a high probability, but worth a quick try.

 

[PWD]

Hi there, still coming up with #NUM! error when I use this:

=AGGREGATE(16,6,(K4:K12)/((SIGN((LEFT(C4:C12,2)="LD")+(C4:C12=”RAT”)))*(K4:K12<>"")),0.5)

But thanks, anyway.

Many thanks,
D€$

 

[SkipVought]

You can use this technique to troubleshoot an expression. You must be careful, however to exit using the ESC key, else your expression will change!!!

Activate the cell containing the formula.

In the Formula Bar, SELECT any complete expression (that would resolve to TRUE/FALSE or a value)

Hit the F9 Key: VOLA, y'all!

Don't forget to ESC to exit this!!!

So playing around with your function, this may work
[tt]
=AGGREGATE(16,6,($K$4:$K$12)/(((LEFT($C$4:$C$12,2)="LD")[highlight #FCE94F]+[/highlight]($C$4:$C$12="RAT"))*($K$4:$K$12<>"")),0.5)
[/tt]

Caaveat: column K must contain a number for every value in column C.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Hi Skip, THANKS for that tip!!

When I examined the "RAT" part, all I got was NAME,NAME,NAME. When I looked at the formula it had a different set of " " marks - I must have copied something from somewhere along the line. Once I corrected that it worked with or without the SIGN part of the formula - I don't know what difference that makes, TBH. Fortunately Column K is always populated as it is "Length of Stay" - actually, does this mean that I don't need the *(K4:K12<>"")? It appears to work well without.

Having discovered this Aggregate Function I was keen to try to use it to discover conditional MAX values so I tried this:

=AGGREGATE(4,6,(K4:K12)/(((LEFT(C4:C12,2)="LD")+(C4:C12="RAT"))))

But this gives #VALUE! error. The second part evaluates to {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

Am I close?

Many thanks,
D€$

 

[SkipVought]

Please post the VALUES in columns C & K.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

How about...
[tt]
=AGGREGATE(16,6,($K$4:$K$12)/(((LEFT($C$4:$C$12,2)="LD")+($C$4:$C$12="RAT"))),1)
[/tt]
I think that you need the k value.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Aggregate, k argument...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Hi Skip, these are the values in Column C

WR
LD,6
RAT
RAT
R,3
R,2
WR
LD,7
Triage

I tried adding the k value - it made no difference - but I don't think it's required for MAX (4,6,....)

If I click in the formula bar and click the fx button, in the Ref1 window it starts with {#DIV/0! but I've no idea why as this is what's in Column K

04:36:10
04:04:10
03:50:10
03:27:10
03:19:10
03:01:10
02:18:10
02:00:10
01:58:10

All I can find "Out there" is someone asking much the same question:-

However, these ones do not and I get a #VALUE!

Max:
AGGREGATE(4,6,IndexData[Yield to Worst]/(IndexData[Sector Level 4]=$B$2)*(IndexData[Composite Rating]=$A4)*(IndexData[MatBucket]=$B$1))

Still stuck

Many thanks,
D€$

 

[PWD]

I thought about trying the LARGE function (14):

=AGGREGATE(14,6,(K4:K12)/((LEFT(C4:C12,2)="LD")*(C4:C12="RAT")),1)

but this gives a #NUM! error.

Many thanks,
D€$

 

[PWD]

I think I've got it!!

=AGGREGATE(14,6,(K4:K12)/((LEFT(C4:C12,2)="LD")+(C4:C12="RAT")),1)

Many thanks,
D€$

 

[Deniall]

Awake again (just).
I'm glad you have found a solution.
However I suspect that your 22Mar16@16:57 formula works only because your two OR'ed conditions are mutually exclusive.[&nbsp;] My use of the SIGN() function was aiming to ensure a more general result, one that would work where the two conditions could both be TRUE simultaneously.

 

[Deniall]

And a bit more awake now.

Obviously your particular two conditions can never be TRUE simultaneously.[&nbsp;] But if, say, each condition applied to different columns, then the SIGN() becomes important. For example:
((LEFT(C4:C12,2)="LD")+(D412="RAT"))

I have quickly tested the compatibility of using SIGN() within AGGREGATE() in this way, and Excel accepts it.

 

[PWD]

Good morning. That's interesting. I've changed the formula as below where Column D is a list of membership numbers:

=AGGREGATE(14,6,(K4:K12)/((SIGN(LEFT(C4:C12,2)="LD")+(D412="1506405"))),1)

Curiously, it appears to ignore the second qualifier and just gives me the largest of Column K that corresponds to LD* in Column C, which is 1506419. What's that all about????

Many thanks,
D€$

 

[Deniall]

If you can post the contents of the three columns (C/D/K) that correspond with your 23Mar16@08:29 post I'll try to have a play. First thought, based on zero evidence, is a problem related to numbers in text form.

 

[PWD]

Thanks, I tried changing Column D from General to Number but it still appears to be the same. Can you remind me again how to get columns of data on here sensibly?

Many thanks,
D€$

 

[Deniall]

I was able to cut&paste your columns C and K directly from your 22Mar16@16:45 post.[&nbsp;] Just do something similar with your column D412.[&nbsp;] Or wrap in TT /TT tags to indicate monospace font.[&nbsp;] Or simplify your spreadsheet so that it shows only the problem area then upload it.

 

[Deniall]

That took me an embarrassingly long time.
Looks like you got lost in your own parentheses (just like I got lost in mine).[&nbsp;] Try changing your
=AGGREGATE(14,6,(K4:K12)/[highlight #EF2929](([/highlight]SIGN[highlight #EF2929]([/highlight]LEFT(C4:C12,2)="LD")+(D412="1506405"))),1)
to
=AGGREGATE(14,6,(K4:K12)/[highlight #EF2929]([/highlight]SIGN[highlight #EF2929](([/highlight]LEFT(C4:C12,2)="LD")+(D412="1506405"))),1)

 

[PWD]

Hi there, thanks for this. I've just got to find which report I was using this for now!!

Many thanks,
D€$

 

[PWD]

Found it!!

Many thanks,
D€$