Randon Sampling

[Guest]

I want to do some random sampling from 1-50 for example in 3 different cells. I dont want the cells to equal each other. If I pull out #34 it is not available to be pulled for the 2nd sample. How do I make it generate random numbers where one cell cannot equal another? Thanks

 

[Ken Wright]

With your data in say A1:A50, in B1:B50 put =RAND(). Copy Col B and then paste
special as values. Now select all and sort on Col B, picking out the top 3.

You can also use a formula with the RAND() functions still in the cells, but you
need to know the data will change every time the sheet calculates. With data as
above and RAND() in each of B1:B50, select any 3 vertical cells and paste the
following into the formula bar, and then array enter it using CTRL+SHIFT+ENTER:-

=INDEX($A$1:$A$50,MATCH(LARGE($B$1:$B$50,{1;2;3}),$B$1:$B$50,0))

You might have to hit F9 to have it calculate once, but that should give you 3
random non-duplicated numbers. As I said though, every time the sheet
calculates they will change so you may need to note them down, or copy and paste
them as values to another section of sheet, which would leave the formulas
intact for next time.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I want to do some random sampling from 1-50 for example in 3 different cells.

I dont want the cells to equal each other. If I pull out #34 it is not available
to be pulled for the 2nd sample. How do I make it generate random numbers where
one cell cannot equal another? Thanks