sampling large data sets

[jedoeller]

Earlier Excel versions contained a Sampling function that allowed the sampling of large data sets, say a digital readout from an instrument. For example, we could get every 10th number to compress the data set. The Excel 2008 for Mac does not seem to contain this function. Does anyone know how to program Excel to do this? Why is it missing from Excel 2008 for Mac?

 

[CyberTaz]

Just so you're not sitting on the edge too long ...

Random Sampling was a part of the Add-Ins supplied with prior versions of
Excel and relied on VBA to function. Since VBA isn't supported in 2008 the
add-ins are gone.

Most - if not everything - of what they supplied can still be accomplished,
but it will take more work for the user. I'll leave it to one of the more
adept contributors to provide you with details, but this link to JE
McGimpsey's site may provide all you need:

http://mcgimpsey.com/excel/udfs/samplenoreplace.html

 

[JE McGimpsey]

Earlier Excel versions contained a Sampling function that allowed the
sampling of large data sets, say a digital readout from an instrument. For
example, we could get every 10th number to compress the data set. The Excel
2008 for Mac does not seem to contain this function. Does anyone know how to
program Excel to do this? Why is it missing from Excel 2008 for Mac?

Last question first: Because the removal of VBA also removed the ability
to run the ATP wizards (though all the ATP functions are now
incorporated into XL).

Sampling every 10th value is simple. Assume the data is in A1:A10000

Use:

B1: =INDEX(A:A,(ROW()-1)*10+1)

Copy B1 down as far as required (i.e., B1000)

 

[Winnie]

How can I randomly select 10% from a database of 50 numbers.

 

[Mike Middleton]

Winnie -

One way is to enter =RAND() in an adjacent helper column, select the data in
both columns (or select all columns of your data along with the helper
column), and sort (Data | Sort ...) using the random numbers as the key.

Then choose the top 5 items as the sample.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel