Maths and VBA (1 Viewer)

[The Member]

Dear Young people of this universe,

Is there any one who can demonstrate to me how to do Integration in VBA / Access?? I have the formula but dun know how... it is the normal distribution equation... I need to have the area under curve...

Option Compare Database
Option Explicit


Dim neK As Double 'neK is the Mathematical Constant e, 2.71828
Dim nPiK As Double 'nPiK is the Mathematical Constant, 3.14159
Dim fx As Double 'f(x) is Y, i.e. Value at Y axies

Dim nMu As Double 'nMu is the Mean of the dataset
Dim nSD As Double 'nSD is the Standard Deviation of the dataset
Dim nx As Double 'nx is the testing point.

'Set Mathematical Constants

Public Function NormalDisCRV(nMu As Double, nSD As Double, nx As Double)

Set neK = 2.71828
Set nPiK = 3.14159

fx = (1 / nSD * ((2 * nPiK) ^ (1 / 2))) * (neK ^ ((-1 / 2) * ((nx - nMu) / nSD) ^ 2))

End Function

I would need to intergrate fx with respect to nx from -00 (infinity) to nx...

fx = (1 / nSD * ((2 * nPiK) ^ (1 / 2))) * (neK ^ ((-1 / 2) * ((nx - nMu) / nSD) ^ 2))

many thanks...

JL

 

[AChir]

I'll give this one some thought but as an initial comment, you write

fx = (1 / nSD * ((2 * nPiK) ^ (1 / 2))) * (neK ^ ((-1 / 2) * ((nx - nMu) / nSD) ^ 2))

I think you need:

fx = (1 / ( nSD * ((2 * nPiK) ^ (1 / 2))) ) * (neK ^ ((-1 / 2) * ((nx - nMu) / nSD) ^ 2))

 

[The Member]

Possible

Dear all young people of this universe,

I am happy to let you know that I now manage to intergrate (well approx.) by using Simpson's Rule in VBA. I have tested it for Y=X to the powers of 1 and 2 and will test it on power 3 and more now.

ultimately I think it is possible to calculate the % under the Normal curve... will keep you all updated...

Big smile

JL

 

[The Member]

Dear all, I have tested the Normal curve and it is working, too happy. I cross checked with the Normal table and it matched.

So this problem solved.

 

[AChir]

Excellent news - I'd be really interested to see the code you have

 

[The Member]

Free for all

Dude,

Simply this will do Approximation for integration for all equations (finger cross, tested on X to the power of 1, 2, 3) it is working on the Normal curve too. at below look for the "a" and "b", this are the lower and upper limit of the equation respectively. These code is now more or less specific for Normal curve but the Idea would work on other equation for approximation too.

I dun expect this is total save for every situation so please let me have your feed back.

JL



Option Compare Database
Option Explicit

Public Sub AreaUnderCurve()

'----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 otherwise known as Simpson's Rule.
'----JL 18/06/2002



Dim X As Double 'Operator in the equation
Dim Y As Double 'Operator in the equation

Dim a As Double 'lower limit
Dim b As Double 'upper limit
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 Area As Double 'Final output


Dim intI As Integer 'intervals
Dim Counter As Integer
Dim neK As Double 'Mathematical Constant
Dim nPiK As Double 'Mathematical Constant

intI = 10000 '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
b = 1.96 '<<<<This can be changed
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 Sub

 

[Cosmos75]

COOL!!

Edit: (July 27th, 2005) I created a sample database and put it on my website. Thought I'd post the link here in case anyone happens to read this old post...


Mods, if this is not allowed, let me know and I'll delete this edit!

 

[The Member]

Free for all... take 2

Dear all,

following code is a revised version of the Normal curve, stick it into the VBA and run it, it shall work. I am thinking at the moment the reverse of it as in Confident interval, say 95%CI gives +/-1.96. will keep this tread updated...

enjoy...

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

Public Function AreaUnderNormalCurve(a As Double, b As Double)

'----info
'Area Under Curve is a approximation for Integrating an equation, it takes on the louer limit and the upper limit and
'uill return the approx. area under the curve.
'This technique is otheruise knoun 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 = 10000 'Set intervals to houever many pieces.
neK = 2.71828182845905 'Set constant value
nPiK = 3.14159265358979 'Set constant value


'a = -1.96 '<<<<This can be changed Louer 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 StandardisedNormalDistribution()
'!!!!!!!!!!!!!!!!!!!!!!!---!!!!!!!!!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!!!!!!-====IXXXX-!!!!!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!!-========IXXXXXXXX-!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!==========IXXXXXXXXXXl!!!!!!!!!!!!!'
'!!!!!!!!!!!-============IXXXXXXXXXXl=-!!!!!!!!!!!'
'!!!!!!!!!-==============IXXXXXXXXXXl===-!!!!!!!!!'
'!!!!!!!-================IXXXXXXXXXXl=====-!!!!!!!'
'-----===================IXXXXXXXXXXl========-----'
'------------------------I----------l------------'
'________________________0__________Z_____________'

Dim b As Double

b = InputBox("Please key in Z")

Call AreaUnderNormalCurve(0, b)

MsgBox (Area), vbInformation

End Sub
Public Sub TuoTailsStandardisedNormalDistribution()

'!!!!!!!!!!!!!!!!!!!!!!!---!!!!!!!!!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!!!!!!-XXXXIXXXX-!!!!!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!!-XXXXXXXXIXXXXXXXX-!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!lXXXXXXXXXIXXXXXXXXXXl!!!!!!!!!!!!!'
'!!!!!!!!!!!-==lXXXXXXXXXIXXXXXXXXXXl=-!!!!!!!!!!!'
'!!!!!!!!!-====lXXXXXXXXXIXXXXXXXXXXl===-!!!!!!!!!'
'!!!!!!!-======lXXXXXXXXXIXXXXXXXXXXl=====-!!!!!!!'
'-----=========lXXXXXXXXXIXXXXXXXXXXl========-----'
'--------------l---------I----------l-------------'
'_____________-Z_________0__________Z_____________'

Dim b As Double

b = InputBox("Please key in Z")

Call AreaUnderNormalCurve(-b, b)

MsgBox (Area), vbInformation

End Sub
Public Sub CumulativeStandardisedNormalDistribution()

'!!!!!!!!!!!!!!!!!!!!!!!---!!!!!!!!!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!!!!!!-====I=== -!!!!!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!!!-========I========-!!!!!!!!!!!!!!!'
'!!!!!!!!!!!!!===========I==========!!!!!!!!!!!!!!'
'!!!!!!!!!!!-============I============-!!!!!!!!!!!'
'!!!!!!!!!-Xl============I==============-!!!!!!!!!'
'!!!!!!!-XXXl============I================-!!!!!!!'
'-----XXXXXXl============I===================-----'
'-----------l------------I------------------------'
'___________Z____________0________________________'

Dim b As Double

b = InputBox("Please key in Z")

Call AreaUnderNormalCurve(-8, b)

MsgBox (Area), vbInformation

End Sub