NORMDIST from Excel

[Guest]

I am trying to use data from an Access table to create a bell curve. I can
do this in Excel but i'm having trouble in access.

Right now I've got a dollar value and 16 payment periods, the periods are
determined by how many months from first to last payment. The payments
should resemble a bell curve when plotted on an x-y scatter graph. I am
using the following in VBA:
Dim objExcel As Excel.Application

Set objExcel = CreateObject("Excel.Application")

Me.intNorm = objExcel.Application.NormDist(Me.intDR, Me.intMean,
Me.intSD, True)
objExcel.Quit

Set objExcel = Nothing
'---END---
Where:
intDR is the payment period or x value (1-16)
intMean is the mean (8.5)
intSD is the standard deviation (4.76095)

I have this behind a continuous form. under Form_Load()

When the form is opened, the output, intNorm, has the normal cumulative
distribution for the first period, 0.05759187, for all the periods.

Does anyone know why this isn't computing for each value?

It should be displaying increasing values, not the same value.

Thanks,

Matt

 

[Tim Ferguson]

I am trying to use data from an Access table to create a bell curve.
I can do this in Excel but i'm having trouble in access.

I didn't understand all the cross-platform stuff, but this may help:

'Option Explicit

'***********************************************************************
'* Cumulative Standard Normal Distribution *
'* (this function provides similar result as NORMSDIST( ) on Excel) *
'* Source: http://www.geocities.com/WallStreet/9245/vba6.htm *
'***********************************************************************
Public Function SNorm2(z As Double) As Double

Const c1 = 2.506628
Const c2 = 0.3193815
Const c3 = -0.3565638
Const c4 = 1.7814779
Const c5 = -1.821256
Const c6 = 1.3302744
Dim w As Double, x As Double, y As Double

If z > 0 Or z = 0 Then
w = 1
Else
w = -1
End If

y = 1 / (1 + 0.231649 * w * z)

x = c6
x = y * x + c5
x = y * x + c4
x = y * x + c3
x = y * x + c2
SNorm2 = 0.5 + w * (0.5 - (Exp(-z * z / 2) / c1) * y * x)

End Function


Hope that helps


Tim F