Solved

Easy way to get a random sample of data in Excel 2007

Posted on 2011-03-25
8
274 Views
Last Modified: 2012-05-11
Hello:

I have a large data set in excel and I would like to know the quickest (and easiest) way to get a random sample of the data.

The data has several different usernames in a column called "Assigned to" and I need to get 10 random records from each user.  

Thank you in advance.

0
Comment
Question by:MeowserM
  • 4
  • 4
8 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 500 total points
ID: 35215661

Sort by usernames. Use the =RANDBETWEEN() function to generate a random number 10 times and pick those rows as your sample.
0
 

Author Comment

by:MeowserM
ID: 35215746
Ok that sounds good.  Question for you:  How do I get the bottom and top numbers for each user?

I created a counter for each record within that users "group" of records.  But how do I get the MIN and MAX of that counter?

Thank you for you help
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35215784
Once you sort your data, then MIN number will be the row where that user's data starts and the MAX number will be where that user's data stops.
0
 

Author Comment

by:MeowserM
ID: 35215843
Yes and the MIN will always be 1 because that's where I start my counter.  But is there a formula or function I can use to determine the MAX?

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 500 total points
ID: 35215935
The MIN should not always be 1 if it is a sorted list. You can use =COUNTIF() funtion to find how many rows there are for each username, then the MAX will be equal to the MIN + COUNTIF(username)
0
 

Author Comment

by:MeowserM
ID: 35216013
Very smart.  That's what I was looking for.

 Thank you I need some extra help today.

0
 

Author Closing Comment

by:MeowserM
ID: 35216021
Thank you again.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35216037
Thanks for the grade.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now