Sampling

[myrtita]

Hello:

I have a database with the sales of an entire month of a department. I have a column with the seller name, one with the sold item and the rest of the table with detail of the item sold. I need to identify a random sample of 25% for each seller (not all of them sold the same amount of items). Is any form of doing this. The 'Rand()' formula doesnt work for what I need...


Thanks,

myrtita

 

[Eric W]

Welcome to the forum!

How many rows is typical for the sales? How do you want the results presented? Do you want the sample highlighted, or do you want it extracted to another sheet?

If you just want them highlighted, you can use a combination of RAND() and Conditional formatting. Add a new column, and put =RAND() in it, and copy it down then length of your data. Then select all the columns, click Conditional Formatting --> New rule --> Use a formula to determine which cells to format, then enter this formula
=$A1>.75
and select a fill color. It should look something like:
Excel 2010

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]0.351674[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0.007325[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0.444376[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0.7017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FCD5B4, align: right"]0.835534[/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]0.001488[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0.488796[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0.148539[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0.441398[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0.692217[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]0.618731[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]0.042641[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FCD5B4, align: right"]0.846518[/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]0.297904[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]0.639236[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FCD5B4, align: right"]0.825545[/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FCD5B4, align: right"]0.899571[/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]0.690012[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]0.341424[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FCD5B4, align: right"]0.849342[/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]0.113017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]0.721248[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]0.608816[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]0.078786[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]0.453977[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

You can select another subset just by pressing F9.
Let us know if that works, or you'd like a different way to look at them.