Data Analysis Sampling (2003)

Topic

New Reply

I have a spreadsheet that contains about 500 rows and 20 columns.
I want to pull a random sampling of 30 from the list. I need all the columns of information pull.
When I use the Data Analysis Sampling, it gives me an error message about the input range contains non-numeric data. If I just pull the medical record it works but then I have to go back and find the rest of the data.
Is there a way to extract all the fields when I use the Sampling feature?

Thanks as always,
Deborah

Reply | Quote

Replies

It sounds like you will have to limit your sampling to just numeric items to use the builtin feature. Can your text entries be coded to be numeric in some way or you could take a subset of the data and only extract the numeric columns.

Steve

Reply | Quote

I have done something similar and well..it is more work. I do not understand why it will not extract all the columns. I try to get the computer to work for me and me not work for it..but apparently I will have to build some coding or do my cutting and pasting routine for this option.

Thanks

Reply | Quote

The message seems straightforward to me, your dataset has non-numeric data in it, either text or error values. These should be removed before attempting to sample.

If your dataset is all constants, you can use select the region,
edit – goto- special,
select “Constants”
uncheck “Numbers”
and [ok] you will select all the non-numeric entries

At this stage can clear them all if you like (blanks are OK in sampling) but this will modify your dataset.

Steve

Reply | Quote

Maybe this formula instead…
=INDEX($B$5:$U$504,RANDBETWEEN(1,500),RANDBETWEEN(1,20))
Assumes your data is in B5:U504 and that you have the Analysis ToolPak installed.
Fill the formula across 30 cells to get 30 random samples. Press the F9 key to refresh.
Jim Cone
Portland, Oregon USA

Reply | Quote

This is not working for me.
When I fill the formula, I get all sorts of data. Not the entire record for that person.
=index($b$2:$u$20,RANDBETWEEN(1,20),RANDBETWEEN(1,3))
I am trying to figure this out before applying to my large worksheet. So I copied 20 records and I only want a random sampling of 3.
What am I doing wrong?

Deb

Reply | Quote

I think what is wrong is that I misinterpreted what you are after.
The formula returns a random cell value from the data not an entire row.
I believe “Data Analysis Sampling” also returns individual cell values not entire rows.
Jim Cone
Portland, Oregon USA

Reply | Quote

That is what I was griping about….LOL
I was not getting all the data that I wanted to extract out in a random sampling.

Thanks for your help. I appreciate your efford.

Reply | Quote

You could do the following:
– In the first empty column adjacent to the data, enter Dummy or some like that as column heading, and fill the rest with formulas =RAND()
– Activate AutoFilter.
– Click the dropdown arrow in the Dummy column, and select Top 10…
– Enter the number of records that you want and click OK.
– You can copy the result to another location; this will include only the filtered records.

Reply | Quote

THANKS….that works.

Reply | Quote