basNormalDistributionINV

[The Member]

Dear young people of the universe...

Last time I have made the basNormalDistribution:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=29798

this time I have the reverse of it, please give me the feed back,

ta

JL






Option Compare Database
Option Explicit
Dim Area As Double 'Final output

Dim b As Double

Public Function AreaUnderNormalCurveINV(a As Double, b As Double)
'----info
'Area Under Curve is a approximation for Integrating an equation, it takes on the lower limit and the upper limit and
'will return the approx. area under the curve.
'This technique is otheruise known as trapezoidal Rule (Courtesy to MB).
'----JL 18/06/2002
Dim X As Double 'Operator in the equation
Dim Y As Double 'Operator in the equation
Dim nSD As Double
Dim nMu As Double
Dim nX1 As Double '
Dim nX2 As Double '
Dim nY1 As Double 'Output variable 1
Dim nY2 As Double 'Output variable 2
Dim intI As Integer 'intervals
Dim Counter As Integer
Dim neK As Double 'Mathematical Constant
Dim nPiK As Double 'Mathematical Constant
intI = 1000 'Set intervals to however many pieces.
neK = 2.71828182845905 'Set constant value
nPiK = 3.14159265358979 'Set constant value
'a = -1.96 '<<<<This can be changed Lower limit
'b = 1.96 '<<<<This can be changed Upper Limit
nSD = 1
nMu = 0
nX1 = 0
nX2 = 0
Area = 0
For Counter = 0 To (intI - 1)
nX1 = a + Counter * ((b - a) / intI)
nX2 = a + ((Counter + 1) * ((b - a) / intI))
X = nX1
Y = (1 / (nSD * ((2 * nPiK) ^ (1 / 2)))) * (neK ^ ((-1 / 2) * ((X - nMu) / nSD) ^ 2))
nY1 = Y
X = nX2
Y = (1 / (nSD * ((2 * nPiK) ^ (1 / 2)))) * (neK ^ ((-1 / 2) * ((X - nMu) / nSD) ^ 2))
nY2 = Y
Area = Area + ((nY2 + nY1) * (nX2 - nX1)) / 2
Next Counter
'MsgBox (Area), vbInformation
End Function
Public Sub TwoTailsStandardisedNormalDistributionINV()
'----info
'This module performs inverse two tails normal distribution,
'it is a iterative techinque and have accurcy to 6 d.p.
'This is specially designed for the Normal curve and should not be used on other intergration models.
'----JL 21/06/2002

'!!!!!!!!!!!!!!!!!!!!!!!---!!!!!!!!!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!!!!!!-XXXXIXXXX-!!!!!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!!-XXXXXXXXIXXXXXXXX-!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!lXXXXXXXXXIXXXXXXXXXXl!!!!!!!!!!!!!'
'!!!!!!!!!!!-==l--------Give--------l=-!!!!!!!!!!!'
'!!!!!!!!!-====l------Confident-----l===-!!!!!!!!!'
'!!!!!!!-======l------Interval-%----l=====-!!!!!!!'
'-----=========lXXXXXXXXXIXXXXXXXXXXl========-----'
'--------------l---------I----------l-------------'
'_____________-Z_________0__________Z_____________'

Dim nm As Double 'CI as a percentage
Dim Counter As Integer 'loop operator (needed only for debugging...)
Dim CIa As Double 'upper
Dim CIb As Double 'middle
Dim CIc As Double 'lower
Dim AreaUpper As Double 'Upper area
Dim AreaMiddle As Double 'middle area
Dim AreaLower As Double 'lower area

Area = 0
CIa = 8 'upper
AreaUpper = 0.5
CIc = 0 'lower
AreaLower = 0


nm = InputBox("Please key in Confident Interval, value range 0<=Z>1")

If nm < 0 Or nm >= 1 Then
MsgBox ("Please give value greater then or equal to 0 and smaller then 1"), vbOKOnly
Exit Sub
End If

nm = nm / 2

While Format(Area, "&&&.000000") <> Format(nm, "&&&.000000")
'For Counter = 0 To 45
CIb = (CIa + CIc) / 2 'Middle
Call AreaUnderNormalCurveINV(0, CIb)
AreaMiddle = Area

If nm < AreaUpper And nm > AreaMiddle Then
CIa = CIa
CIc = CIb
AreaUpper = AreaUpper
AreaLower = AreaMiddle
ElseIf nm < AreaMiddle And nm > AreaLower Then
CIa = CIb
CIc = CIc
AreaUpper = AreaMiddle
AreaLower = AreaLower
End If
'MsgBox (CIa), vbInformation 'for debugging
'MsgBox (CIb), vbInformation 'for debugging
'MsgBox (CIc), vbInformation 'for debugging
'Next Counter
Wend
'MsgBox (CIa), vbInformation 'for debugging
MsgBox (CIb), vbInformation 'result
'MsgBox (CIc), vbInformation 'for debugging
End Sub