random draw without replacement from a distribution

[Niels]

Hello,

I have the following problem. I managed to make a random draw without
replacement from
http://www.staff.city.ac.uk/r.j.gerrard/excelfaq/faq.html#sample:

"If the population is in Column A, and Column B is filled with =RAND(), then
enter the formula =INDEX(A:A,RANK(B1,B:B)) in C1 and copy down to Row n,
assuming a sample of size n is required."

Now, I have a set of point localities in geographic space each with an
unique identifier indication a location in geographic space. At certain
localities I have multiple points. Now I want to select 5 random points with
no duplication from the identifier column

identifier
1
2
2
3
4
4
4
5
5
6
7
7
8
9
9
10

So, how can I automatically randomly draw 5 unique values from this list.
Thanks

Niels

 

[RagDyeR]

I would think that you'll first need to make a unique list of your points,
and then draw your random samplings from that unique list.

With your list in say A1 to a20,
And the Rand() function in Column B,
Enter this in C1:

=A1

Enter this *array* formula in C2:

=IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$20&""),0)),"",INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(C$1:C1,$A$1:$A$20&""),0)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE entry, copy down 'til you get blanks.

This is now the datalist to draw from.

Enter this formula in D1:

=INDEX(C:C,RANK(B1,$B$1:INDEX(B:B,COUNT(C:C))))

And copy down as needed.

Now, each hit of <F9> will return a new random list.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Hello,

I have the following problem. I managed to make a random draw without
replacement from
http://www.staff.city.ac.uk/r.j.gerrard/excelfaq/faq.html#sample:

"If the population is in Column A, and Column B is filled with =RAND(), then
enter the formula =INDEX(A:A,RANK(B1,B:B)) in C1 and copy down to Row n,
assuming a sample of size n is required."

Now, I have a set of point localities in geographic space each with an
unique identifier indication a location in geographic space. At certain
localities I have multiple points. Now I want to select 5 random points with
no duplication from the identifier column

identifier
1
2
2
3
4
4
4
5
5
6
7
7
8
9
9
10

So, how can I automatically randomly draw 5 unique values from this list.
Thanks

Niels

 

[Niels]

Wow, this works indeed as I described. And thank you for introducing arrays
(-; The only thing is that I did not completely describe my problem as I had
in mind. I should have included density dependent. If I again refer to the
list below, I want to randomly draw 5 unique values but the chance of
drawing 4 should be 3 times as high as drawing 1 etc. Something like a
density dependent random draw of unique values. I hope you or anybody else
can help me out, best,

Niels

 

[RagDyeR]

I see that you started a new thread on this subject.
Although that's usually not recommended, since you did include this thread
in your question, that should bring new responders up-to-date.

And since your question is now out of my league, stick with the new thread.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Wow, this works indeed as I described. And thank you for introducing arrays
(-; The only thing is that I did not completely describe my problem as I had
in mind. I should have included density dependent. If I again refer to the
list below, I want to randomly draw 5 unique values but the chance of
drawing 4 should be 3 times as high as drawing 1 etc. Something like a
density dependent random draw of unique values. I hope you or anybody else
can help me out, best,

Niels