Expert Microsoft Excel help please - for my DH

[Hillbeans]

My DH has a very specific question for anyone who is very proficient in Excel. I told him i'd pose it to my very smart DIS community:

Using the data analysis tools, the sampling feature, one of the options is a random sample. Duplicate values are returned in the sample. Is there a way to prevent this?




Thanks for any help you can provide.

 

[Hillbeans]

bump

 

[Bob Slydell]

What type of random number generation is he doing?

 

[ToyStory Fan]

My gut tells me no. What's probably happening is that either he has repetitive data in his population causing the duplication, or he has chosen a sample size that causes the random selection to cycle through the population too many times.

Another way to do this is to select a sample is to number the population and then generate a random number within the range, and then another random number to increment your selection...i.e. you have a range of 100 items in your population...select a random number from 1-100, write it down...then select another random number that falls within the sample size (i.e. you want to look at 20 values within the original 100) , starting from the number you selected in the first part, count off the number in the second part until you have selected enough items for your sample size.

Hope that helps.

 

[Fishbone†]

Unfortunately, my Excel doesn't have the data analysis toolpak installed, and without knowing all that he's doing, I don't know how much I can help, but I THINK there is an option to choose the "number of samples". Maybe where you choose "random" as your sampling choice, there is a box to enter the number of samples?

Again, I'm shooting blind, but see if that's a feasible solution.... or something of the sort.

 

[Fishbone†]

Oh, I think I misunderstood - never mind what I said.


I don't think you can prevent this, but................ what ToyStory Fan said.

 

[caitycaity]

Originally posted by ToyStory Fan

Another way to do this is to select a sample is to number the population and then generate a random number within the range, and then another random number to increment your selection...i.e. you have a range of 100 items in your population...select a random number from 1-100, write it down...then select another random number that falls within the sample size (i.e. you want to look at 20 values within the original 100) , starting from the number you selected in the first part, count off the number in the second part until you have selected enough items for your sample size.

Hope that helps.

there isn't really way to prevent random numbers from repeating themselves using the excel random number generator function. even if the data you have does not repeat values, the number generator in excel uses the total data for each draw and there is no way to tell it not to (of course, if you told it not to, then it wouldn't really be random).

i don't know what kind of work he is doing, but i have found that true statistics packages are usually better at random number generation. they certainly offer you more options.

frequently, i will use the random number generator from a statistics program and apply it to data in another format.

 

[Hillbeans]

Thanks everyone. I think he's in complete agreement with all of you that there really is no way to get a proper random sampling from Excel.
I appreciate all of your help.