Thread: Using Small

Hi all

How would I add the SMALL function to the formula below to ensure my results
were sorted?

=INDEX($A$3:$A$11,MATCH(0,COUNTIF($B$2:B2,$A$3:$A$11),0))

Further more, would anyone be so kind as to explain how the SMALL function
works in a formula like this or point me in the right direction of an
explanation?

=INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($A$2:$A$11,"<"&
$A$2:$A$11),ROW(1:1)),COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),0))

It works by counting how many items are smaller than each data element, and
then uses SMALL with the row number to extract from the list that count,
which it matches back against the same count list to get the index, which it
passes to INDEX to get the actual element.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PH NEWS" <steven.lancaster@phresearch.com> wrote in message
news:44c782e8$0$15046$db0fefd9@news.zen.co.uk...
> Hi all
>
> How would I add the SMALL function to the formula below to ensure my
results
> were sorted?
>
> =INDEX($A$3:$A$11,MATCH(0,COUNTIF($B$2:B2,$A$3:$A$11),0))
>
> Further more, would anyone be so kind as to explain how the SMALL function
> works in a formula like this or point me in the right direction of an
> explanation?
>
>

Cheers Bob. Very good
"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:#g8baqMsGHA.5020@TK2MSFTNGP05.phx.gbl...
> =INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($A$2:$A$11,"<"&
> $A$2:$A$11),ROW(1:1)),COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),0))
>
> It works by counting how many items are smaller than each data element,
and
> then uses SMALL with the row number to extract from the list that count,
> which it matches back against the same count list to get the index, which
it
> passes to INDEX to get the actual element.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "PH NEWS" <steven.lancaster@phresearch.com> wrote in message
> news:44c782e8$0$15046$db0fefd9@news.zen.co.uk...
> > Hi all
> >
> > How would I add the SMALL function to the formula below to ensure my
> results
> > were sorted?
> >
> > =INDEX($A$3:$A$11,MATCH(0,COUNTIF($B$2:B2,$A$3:$A$11),0))
> >
> > Further more, would anyone be so kind as to explain how the SMALL
function
> > works in a formula like this or point me in the right direction of an
> > explanation?
> >
> >
>
>