large function in non-continguous array?

[Ian Elliott]

Thanks for any help.
Is it possible to use the LARGE function in a non-continguous array? It
doesn't seem possible.
I got a couple arrays, all in the same column, but with rows in between them.
Thanks again.

 

[Chip Pearson]

Try something like the following:

=LARGE((A1:A5,A11:A15,A21:A25),2)

The test ranges are separated in the formula by commas, Excel's Union
operator, and together are enclosed in parens.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gary''s Student]

Very simple.
Say in A1 thru A24 we have:

1
2
3
4
5
6
7
8
9
10

9999

140
150
160
170
180
190
200
210
220
230
240


However, we want to use LARGE over two disjointed ranges A1:A10 and A14:A24.

This will allow us to "exclude" the 9999 in A12.

First select both A1 thru A10 and A14 thru A24 (use click for the first
group and CNTRL click for the second).

Then pull-down:

Insert > Name > Define... and give the disjoint range the name ian.

Finally in another cell enter:
=LARGE(ian,1)
It will display the 240 and not the 9999