[AccessD] z score to percentile calculator

Thinking about this a bit more, I'd probably use the function below once to build a look up 
table.

On second thoughts, I probably build a lookup table for speed:

Function CreateTable()
Dim strsql As String
Dim i As Currency  'use Currency to avoid rounding errors in ZScores
Dim xlapp  As New Excel.Application
strsql = "Create table tblZScoreToPercentile (ZScore Double primary key,Percentile double)"
CurrentDb.Execute strsql
For i = -5 To 5 Step 0.01
   CurrentDb.Execute "Insert into tblZScoreToPercentile (ZScore,Percentile) values(" & i & "," 
& xlapp.NormSDist(i) & ")"
Next
 xlapp.Quit
 Set xlapp = Nothing
End Function

-- 
Stuart


On 23 Oct 2008 at 16:57, Stuart McLachlan wrote:

> Oops, try this:
> 
> Simplest way is to set a reference to Excel and then use the NormSDist() 
> function:
>  
>  Function NormSDist(ZScore As Double) As Double
>  Dim xlapp  As New Excel.Application
>  NormSDist = xlapp.NormSDist(ZScore)
>  xlapp.Quit
>  Set xlapp = Nothing
>  End Function
> -- 
>  Stuart
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com