Prev: Multiple AND OR functions
Next: VBA to autofill from relative location
From: EricB on 30 Apr 2010 10:31
How do I sample data in a EXCEL spreadsheet? Say I have 50.000 records and I
want to sample say 0.1% or 50, what function do I use?
From: Mike H on 30 Apr 2010 10:43
eric.

It depends what you mean by sample. This will average the 50 smallest
numbers in the range B1 B5000.

You can change average to sum and small to large. You can alter the 1:50 to
(say) 100:150

It's an array

=AVERAGE(SMALL(B1:B50000,ROW(INDIRECT("1:50"))))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"EricB" wrote:

> How do I sample data in a EXCEL spreadsheet? Say I have 50.000 records and I
> want to sample say 0.1% or 50, what function do I use?
From: Jacob Skaria on 30 Apr 2010 10:44
Check out this 8min video on random sampling..(I dont have access to check
and so not sure whether ths is avaible now )

http://www.youtube.com/watch?v=SoK9kq-0uXg

--
Jacob (MVP - Excel)


"EricB" wrote:

> How do I sample data in a EXCEL spreadsheet? Say I have 50.000 records and I
> want to sample say 0.1% or 50, what function do I use?
 | 
Pages: 1
Prev: Multiple AND OR functions
Next: VBA to autofill from relative location