Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 46
Default large function in non-continguous array?

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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default large function in non-continguous array?

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)






"Ian Elliott" wrote in message
...
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.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default large function in non-continguous array?

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

--
Gary''s Student - gsnu200773


"Ian Elliott" wrote:

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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Continguous Cell Entries ConfusedNHouston Excel Discussion (Misc queries) 1 March 12th 08 08:35 PM
Calculating Median Value in a large array willcozz Excel Discussion (Misc queries) 5 December 11th 06 11:21 PM
How do I paste horizontal references in large array Dave Kaleel Excel Worksheet Functions 0 February 6th 06 04:51 PM
how to find duplicate cells in large array of numbers wonkywombat Excel Worksheet Functions 3 August 17th 05 09:57 PM
Average non continguous cells, excluding zero's Keithlearn Excel Worksheet Functions 2 April 27th 05 02:22 PM


All times are GMT +1. The time now is 11:47 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"