LARGE Function with multiple criteria

[ExcelHelpPls]

Greetings,

I'm hoping someone can help me with this problem that I'm guessing there is an easy answer too (but i can't figure it out).

I'm trying to find the nth largest value of a column "C" based on criteria from both column "A" and "B". For example:

A B C
Yes 1 500
Yes 5 600
No 1 800
Yes 2 900

The logic I need to folllow is "If column A = "Yes" and column B = "1 or 2 or 3" return the nth largest value in column C of all values that meet those two requirements.

I was able to use the LARGE function with an IF statement to return the correct value of one criteria (LARGE(IF(A1:A5="Yes",C1:C5),1), but can't figure out how to add the second criteria.

Any help would be greatly appreciated.

Thank you

 

[T. Valko]

Greetings,

I'm hoping someone can help me with this problem that I'm guessing there is an easy answer too (but i can't figure it out).

I'm trying to find the nth largest value of a column "C" based on criteria from both column "A" and "B". For example:

A B C
Yes 1 500
Yes 5 600
No 1 800
Yes 2 900

The logic I need to folllow is "If column A = "Yes" and column B = "1 or 2 or 3" return the nth largest value in column C of all values that meet those two requirements.

I was able to use the LARGE function with an IF statement to return the correct value of one criteria (LARGE(IF(A1:A5="Yes",C1:C5),1), but can't figure out how to add the second criteria.

Any help would be greatly appreciated.

Thank you

Try something like this...

I've assumed the numbers in column B will range from 1 to n. No zeros, no empty cells and no negative numbers.

Array entered**:

=LARGE(IF(A2:A5="yes",IF(B2:B5<=3,C2:C5)),n)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Where n = the nth largest value that you want.

 

[Desu Nota from Columbus]

=IF(AND(A1="yes",OR(B1=1,B1=2,B1=3)),LARGE($C$1:$C$8,nth))

Edit array range to match.

 

[ExcelHelpPls]

T. Valko: I used dummy data to keep the post simple, but my column "B" is not a number, but a text (so instead of 1, "Tier 1").


Desu Nota: I tried yours, but it's throwing a "FALSE" for the output. It might be only checking one cell for the criteria?

Thank you very much for the respones.

 

[taurean]

See if the below formula is of use:

=IF($B3<=3,LARGE(IF(--($A$3:$A$6="Yes"),$C$3:$C$6),$B3),"")

Saw your post after posting :D

=IF(ABS(RIGHT($B3,1))<=3,LARGE(IF(--($A$3:$A$6="Yes"),$C$3:$C$6),ABS(RIGHT($B3,1))),"")

 

[balajayan]

Hi,

Try the below:

LARGE(IF((A1:A7="YES")*((B1:B7="Tier 1")+(B1:B7="Tier 2")),C1:C7),2)

The above is array formula. Please press Control+Shift+Enter instead just Enter after keying in the formula.
Modify the range and constant values as per your requirement.

 

[T. Valko]

T. Valko: I used dummy data to keep the post simple, but my column "B" is not a number, but a text (so instead of 1, "Tier 1").
Thank you very much for the respones.

That is why you should NEVER use dummy data!

Ok, try this...

Book1
	A	B	C
2	Yes	Tier 1	83
3	No	Tier 2	14
4	No	Tier 2	44
5	No	Tier 2	65
6	Yes	Tier 3	20
7	Yes	Tier 1	5
8	No	Tier 4	92
9	Yes	Tier 4	42
10	No	Tier 1	74
11	_	_	_
12	Yes	Tier 1	5
13	_	Tier 2	_
Sheet1

This array formula** entered in C12:

=LARGE(IF(A2:A10=A12,IF(ISNUMBER(MATCH(B2:B10,B12:B13,0)),C2:C10)),n)

Where n = the nth largest value you want. In the sample N=2.

 

[ExcelHelpPls]

Thank you all for your help!

balajayan's solution seems to have worked. I will try out the others as well when i get a minute and report back for anyone else that may use the info on this thread.

Thanks again!

 

[balajayan]

Thanks for the quick feedback.

 

[mtyrrell13]

Thanks for the quick feedback.

balajayan - I am trying a variation of your suggested code and coming up with an error

'{=LARGE(IF((E21:E1000="ACTIVE")*((S2:S1000="ORG A")+(J2:J1000=,">="&Z2)),M2:M1000),1)}

What I am trying to do is build an array of "ACTIVE" records only, that are owned by "ORG A", that have an end date on or before 12/31/13 (the value in Cell Z2), the return the Largest value in Column M that meet the above criteria