large function in non-continguous array?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian Elliott - 13 Mar 2008 21:28 GMT

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.

Reply to this Message

Chip Pearson - 13 Mar 2008 21:45 GMT

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.

Signature

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

> 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.

Reply to this Message

Gary''s Student - 13 Mar 2008 21:49 GMT

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

Signature

Gary''s Student - gsnu200773

Reply to this Message