NORMINV statistical function

[DoDev]

I need a statistical function which calculates an inverse of the normal
cumulative distribution. Visual Basic for Excel supports NORMINV function
which is exactly what I need. Do you know if there is a library for Visual
Basic for Access which includes NORMINV function?

Thanks a lot for your help.

DoDev

 

[Daniel Pineault]

You could reference the Excel library and tap into its worksheet function.
Something like:

Application.WorksheetFunction.NormInv(0.5, 4, 1)

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Daniel Pineault]

Here is a concrete example that I quickly pieced together to help you out.

Function GetXLWkSHtFuncVal()
Dim xlApp As Object
On Error GoTo Error_Handler

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False 'Control whether or not Excel should be visible to
'the user or not.

GetXLWkSHtFuncVal = xlApp.WorksheetFunction.NormInv(0.25, 4, 1)

xlApp.Quit 'Close the instance of Excel we create

Error_Handler_Exit:
On Error Resume Next
Set xlApp = Nothing
Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: GetXLWkSHtFuncVal" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[DoDev]

Hi Daniel,

Thank you so much for your quick response.

I used your example in my program. It works fine.

I just have two concerns:

1. Does this solution impose a requirement that all the future users of my
program wil have to have Excell installed on their PCs? I would rather avoid
imposing this type of requirement.

2. I am concerned about the performance. I will need to invoke Excel
thousands of times while the user is waiting for an answer. While I still
need to perform performance testing I have a feeling that a "native"
statistical library (if such exists) would be faster.


My concerns aside, I do appreciate your help. Your snippet is very useful.

Best,

DoDev

 

[Daniel Pineault]

Yes, my code does require Excel to work.

Your other alternative is to create your own procedure to do the math.
Stats typically is nothing more than basic math, so theorectically (I haven't
done it) you could create your own function(s). Or do some googling, perhaps
someone has already created vba procedures for this exactly scenario that you
could use.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Daniel Pineault]

It isn't exactly what you are looking for, but it may help get you there, a
few stat functions can be found at:

http://support.microsoft.com/default.aspx/kb/209839
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[DoDev]

Your other alternative is to create your own procedure to do the math.

Dan,

You are absolutely right. I am hoping that someone in this forum will point
me to a good library so I don't have to develop my own.

Best,

DoDev