SMALL and LARGE

T

Tonto

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
 
A

andrew.taylor

=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
 
B

Bob Phillips

=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)
 
T

Tonto

Bob

My test array is 12 values between F156 to F167

Please can you talk me through your solution?

Thanks

John
 
B

Bob Phillips

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:F167)-CELL("Row",F156:F167)+
1,F156:F167),2)

and

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

Don't forget ... array formulae

--

HTH

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