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

=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

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

John

=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

Bob

My test array is 12 values between F156 to F167

Please can you talk me through your solution?

Thanks

John

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

Thanks Bob,

Clever, as usual.

Cheers


John