Kth largest element from multiple ranges

[ChetShannon]

Anyone know how to create a formula for the kth largest element of say two different ranges? Preferably done via an Excel formula instead of VBA? In other words I have two ranges and I want to find the k-th largest element between those two ranges. I don't think the LARGE command will allow more than one range to my knowledge.
Thanks! Chet

 

[Eric W]

This should work if the ranges are on the same sheet:

Book1
	A	B	C	D
1	1
2	3	2		8
3	5	4
4	7	6
5	9	8
6		10
7		12
8		14
Sheet6

Cell Formulas
Range	Formula
D2	D2	=LARGE((A1:A5,B2:B8),5)

 

[ChetShannon]

Unfortunately the two ranges are on two different sheets. Any other options in that case? Thx! Chet

 

[FormR]

Any other options in that case?

Here is one you can try.

=AGGREGATE(14,6,CHOOSE({1,2},Sheet1!A1:A5,Sheet2!B2:B8),5)

 

[Eric W]

Or a slight variation of FormR's formula:

=AGGREGATE(14,6,IF({0,1},A1:A5,Sheet2!$G$1:$G$3),3)

k in both formulas is the last number. FormR's formula is easier to adapt for more ranges than 2.

 

[jmacleary]

A bit clunky, but you could reference the range on the other sheet in a range on the sheet with the formulas and then include that range. Something like:

Book1
	A	B	C	D	E
1			Range on this sheet:		Range from other sheet:
2	5		11		3
3			13		5
4			5		7
5			8		0
Sheet2

Cell Formulas
Range	Formula
A2	A2	=LARGE((C2:C5,E:E),5)
E2:E5	E2	=Sheet1!C2

 

[ChetShannon]

Thank you for the responses!... One follow-up question though is I see this formula from EricW of
=AGGREGATE(14,6,IF({0,1},A1:A5,Sheet2!$G$1:$G$3),3) but what is the IF{0,1} part of the formula for. I didn't follow that.. Thanks!

 

[Eric W]

Excel doesn't like ranges pointing to different sheets in some cases (like this one). But you can sometimes get around it by converting the ranges to internal arrays. FormR used CHOOSE to do that, I used IF. An IF statement has the basic form:

=IF(condition,TRUE result, FALSE result)

The condition part should return a TRUE/FALSE value, and based on that, the proper result is chosen. You can also use a number to return a TRUE/FALSE value. 0=FALSE, and any non-zero number=TRUE. And if you want to use multiple values, you can put them in an array using the {} braces. So this part of the formula

=IF({0,1},

is giving IF 2 values, a 0 and a 1, which are equivalent to FALSE and TRUE. So in essence, it's saying give me both the FALSE and the TRUE results. Then the 2 arrays are (more or less) intermingled, and the LARGE subfunction of AGGREGATE looks at the mingled array. The "ignore errors" option of AGGREGATE is required, since if the 2 ranges are different sizes, some elements of the arrays will be errors.

If you use CHOOSE, like FormR, you can extend his formula just by increasing the array constant like this:

=CHOOSE({1,2,3},Range1,Range2,Range3)

 

[ChetShannon]

An additional follow-up question. I am using the formula =AGGREGATE(15,6,CHOOSE({1,2},Sheet1!A1:A5,Sheet2!B2:B8),5) in order to find the SMALLest n-th items. The problem I am having is that the source ranges contain some valid times in contiguous sections (from top of range down n rows) and the rest of the lower cells are all zeros. The AGGREGATE formula is picking up the zero items as it should, but I don't want those zero data cells. I found out that if I make the range only to include the data that has actual times then it works. I've been trying to dynamically have the end of the range that has valid times in the formula but that isn't quite working. Is there some easier way to exclude the zero values when trying to find the n-th smallest items from multiple ranges?

 

[Eric W]

One quick way:

=AGGREGATE(15,6,1/(1/CHOOSE({1,2},Sheet1!A1:A5,Sheet2!B2:B8)),5)

This finds the reciprocal of the numbers. If 0, this creates a #DIV/0! error. Then it does the reciprocal again to restore the values for the non-0 numbers. Then AGGREGATE ignores the #DIV/0! errors.