#1   Report Post  
Old October 26th 05, 10:45 PM
Tonto
 
Posts: n/a
Default SMALL and LARGE

Hello everyone.

I have a column of numbers in an array called TEST

1
1
3
4
5
6
7
7

Why when I run the formula =small(test,2) I get 1 and with
=large(test,2) I get 7.

My logic suggests I should get 3 and 6?

Please help.

Thanks in anticipation.


John


  #2   Report Post  
Old October 26th 05, 11:00 PM
 
Posts: n/a
Default SMALL and LARGE

=SMALL (test,N) returns the Nth value when the elements
of test are arranged in ascending order, so

=SMALL(test,1) = 1
=SMALL(test,2) = 1
=SMALL(test,3) = 3
etc. Similarly for LARGE. You have to be a bit cleverer to
get the Nth smallest number ignoring duplicates.

Andrew Taylor


Tonto wrote:
Hello everyone.

I have a column of numbers in an array called TEST

1
1
3
4
5
6
7
7

Why when I run the formula =small(test,2) I get 1 and with
=large(test,2) I get 7.

My logic suggests I should get 3 and 6?

Please help.

Thanks in anticipation.


John


  #3   Report Post  
Old October 26th 05, 11:04 PM
Tonto
 
Posts: n/a
Default SMALL and LARGE


Thanks. I think I pointed that out but still need a solution.

John

  #4   Report Post  
Old October 26th 05, 11:26 PM
Bob Phillips
 
Posts: n/a
Default SMALL and LARGE

=LARGE(IF(MATCH(S1:S10,S1:S10,0)=ROW(Data)-CELL("Row",S1:S10)+1,S1:S10),2)

=SMALL(IF(MATCH(S1:S10,S1:S10,0)=ROW(Data)-CELL("Row",S1:S10)+1,S1:S10),2)

array formulae, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tonto" wrote in message
oups.com...

Thanks. I think I pointed that out but still need a solution.

John



  #5   Report Post  
Old October 26th 05, 11:37 PM
Tonto
 
Posts: n/a
Default SMALL and LARGE

Bob

My test array is 12 values between F156 to F167

Please can you talk me through your solution?

Thanks

John



  #6   Report Post  
Old October 27th 05, 12:01 AM
Bob Phillips
 
Posts: n/a
Default SMALL and LARGE

Essentially, it builds an array of the data, replacing any duplications with
FALSE, and then does a LARGE or SMALL on that enhanced array, so that the
second largest is then truly the second largest if not the second ranked.

The MATCH(S1:S10,S1:S10,0) builds an array of indexes of the data within the
formula (as against a range S1:S10)

The ROW(S1:S10)-CELL("Row",S1:S10)+1 builds an array of possible indexes,
1-10 in this example. In my example, I could have used just ROW(S1:S10) as I
started in row 1, but the rest is proofing regardless of row start.

Comparing one against the other gives an array of TRUE/FALSE which drives
which items in S1:S10 are out-selected by the IF statement, i.e. giving an
array of unique numbers, which is then passed to the LARGE or SMALL
function.

Your formulae would then look like

=LARGE(IF(MATCH(F156:F167,F156:F167,0)=ROW(F156:F1 67)-CELL("Row",F156:F167)+
1,F156:F167),2)

and

=SMALL(IF(MATCH(F156:F167,F156:F167,0)=ROW(F156:F1 67)-CELL("Row",F156:F167)+
1,F156:F167),2)

Don't forget ... array formulae

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tonto" wrote in message
oups.com...
Bob

My test array is 12 values between F156 to F167

Please can you talk me through your solution?

Thanks

John



  #7   Report Post  
Old October 27th 05, 07:02 AM
Tonto
 
Posts: n/a
Default SMALL and LARGE

Thanks Bob,

Clever, as usual.

Cheers


John



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
Troubel with compression of a very large Excel file Thorsten Pollmeier Excel Discussion (Misc queries) 3 January 15th 08 01:15 PM
How do I copy a LARGE selection of cells from Excel to powerpoint? Deadly Excel Discussion (Misc queries) 2 August 15th 05 03:21 PM
Finding LARGE value within range of lookup table WPA Excel Discussion (Misc queries) 2 June 13th 05 07:41 PM
Problems when printing large workbooks Jayne Excel Discussion (Misc queries) 0 March 8th 05 03:43 PM
Attaching labels when using large or small functions BobT Excel Worksheet Functions 1 February 18th 05 11:03 PM


All times are GMT +1. The time now is 05:39 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017