Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Analysis Sampling (2003)

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Analysis Sampling (2003)

    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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Analysis Sampling (2003)

    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Analysis Sampling (2003)

    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 <del> can clear them all if you like (blanks are OK in sampling) but this will modify your dataset.

    Steve

  5. #5
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Data Analysis Sampling (2003)

    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

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Analysis Sampling (2003)

    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

  7. #7
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Data Analysis Sampling (2003)

    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

  8. #8
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Analysis Sampling (2003)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Analysis Sampling (2003)

    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.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Analysis Sampling (2003)

    THANKS....that works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •