In article . com,
Bernd P wrote:

Hello,

SMALL does not work if data values are not unique.

A small example how it might work:
Enter into A1:B3:
37 =RAND()
13 =RAND()
57 =RAND()

Enter into C1:
=COUNTIF($B$1:$B$3,"<"&B1)+COUNTIF($B$1:B1,"="&B1)
and copy down to C3

If you now want to select 2 random values:
Enter into D1:
=INDEX($A$1:$A$3,MATCH(ROW(),$C$1:$C$3,))
and copy down to D2

Enter into E1:
=COUNTIF($D$1:$D$2,"<"&D1)+COUNTIF($D$1:D1,"="&D1)
and copy down to E2

Enter into F1:
=INDEX($D$1:$D$2,MATCH(ROW(),$E$1:$E$2,))
and copy down to F2

Columns B:D do the random selection, E:F sort the result.

This approach works also with text, not only with numbers. I suggest
to replace any SMALL or LARGE approach by the COUNTIF + COUNTIF
approach as shown above.

Regards,
Bernd


Bernd -

SMALL() gives ties (non-uniques) the same place. If there are ties,
what difference can it make? Doesn't SMALL($B$1:$B$3,ROW()) go to the
same thing?

....best, Capt N.

--
Email to (yes, you can so figure it out) ;-]

Scream and shout and jump for joy! I was here before Kilroy!

Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy