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