Determine Valid Sample Size from a Population - Any UDFs or Formulas?

[DataFreakFromUtah]

Hello!

Excel 2000
Win 2000

Does anyone have a custom VBA function that would
determine a general statistically valid sample size for a known population?

I've searched all over Google and in this Excel group and the worksheefunctions
group with no luck.

Essentially the function (or manual Excel formula) would require three inputs:

1. Confidence Level, either 95 or 99
2. Confidence Interval, a number between .1 and 50
3. The Population size

The output would be the sample size.

Or instead of a custom function, does anyone have a manual worksheet formula handy?

Thank you for any information you may provide,
Tom


Search terms: determine sample size calculate sample size
valid sampling valid sample proper sample size proper sampling
calculating sample size correct sample size

 

[K Dales]

Sample size is a tricky subject... the formulas used will
depend on what type of distribution your data fits
(normal/gaussian, binomial, poisson...) and will require
an estimate of the probability. That is probably why you
are having trouble finding a simple formula.

-----Original Message-----
Hello!

Excel 2000
Win 2000

Does anyone have a custom VBA function that would
determine a general statistically valid sample size for a known population?

I've searched all over Google and in this Excel group and the worksheefunctions
group with no luck.

Essentially the function (or manual Excel formula) would require three inputs:

1. Confidence Level, either 95 or 99
2. Confidence Interval, a number between .1 and 50
3. The Population size

The output would be the sample size.

Or instead of a custom function, does anyone have a

manual worksheet formula handy?

 

[Tom Ogilvy]

You can dig the formula out of any basic statistics book. As I recall, the
calculation for means would require an assumption about the population
standard deviation. The calculation for percent would require an
assumption about the population percent (worst case is 50%)

 

[DataFreakFromUtah]

No question here, just a function for the archive.

For what it's worth, here's a quick function for getting a "quick"
estimate for a valid sample size based on the assumptions below.


Function SampleSizeQuickAt95PercentCL(ConfidenceInterval As Double,
Population As Double) As Long
'Calculates a "quick" necessary sample size from a Finite Population
'based on these assumptions:
'1. Normal Distribution
'2. Confidence Level = 95%
'3. Response Rate = 50%
'References: http://www.surveysystem.com/sscalc.htm

Dim SampleSize As Long
Dim CorrectionForFinitePopulation As Long

On Error Resume Next
SampleSize = ((1.96 * 2) * 0.5 * (1 - 0.5)) / (ConfidenceInterval
* ConfidenceInterval)
CorrectionForFinitePopulation = SampleSize / (1 + ((SampleSize -
1) / Population))

SampleSizeQuickAt95PercentCL = CorrectionForFinitePopulation

End Function