Hello everyone,
I would like to run regression analysis in VBA. I have a set of data that I want to fit into a LogNormal distribution. I called the parameters of the Lognormal distribution "sigma" and "mju".
To accomplish this, I am trying to create a function in VBA, called "regreslognormalsigma". This function requires the following ranges as input:
- probabilitetet --> this range contains the cumulative density of the argument;
- abshisat --> this range contains the arguments, in my case these are displacements of a structure in a seismic event.
I can not figure out why the output is always the initial value that I set for "sigma", i.e. the output is always 0.0001.
Can anyone help me out? I would like the function to give me the value of sigma for which the data fit best to a lognormal distribution with a standard deviation sigma. In the meantime, I think the algorithm below can be used to find the mean value also (with little modification, which I plan to do once I solve the issue).
The code is below:
Function regreslognormalsigma(probabilitetet As Range, abshisat As Range) As Double
Dim nrqeliza As Double 'this I use to count the range
Dim Yi As Double
Dim xi As Double
Dim Li As Double
Dim i As Double
Dim j As Double
Dim sigma, mju As Double
Dim sigmafinal, mjufinal As Double
Dim shgk, shgkmin, shgkvar As Double ' these are respectively: the sum of errors squared, the minimum sum of errors squared, and an auxiliary value
Dim result As Double
nrqeliza = probabilitetet.Count
sigma = 0.0001 'initial value
mju = 0.0001 'initial value
shgkmin = 1E+300 'I set a large value for the minimum
shgk = 0 'initial value
result = 0
'Li = WorksheetFunction.LogNorm_Dist(xi, sigma, mju, True) --> this is how I take values of the Log Normal distribution
While sigma < 10
While mju < 10
For i = 2 To nrqeliza
Li = WorksheetFunction.LogNorm_Dist(abshisat(i), sigma, mju, True)
shgk = (((probabilitetet(i) - Li)) ^ 2) + shgk
Next i
If shgk < shgkmin Then
shgkvar = shgk
result = sigma
shgkmin = shgkvar
Else: shgkmin = shgkmin
End If
mju = mju + 0.0001
Wend
sigma = sigma + 0.0001
Wend
regreslognormalsigma = result
End Function
I would like to run regression analysis in VBA. I have a set of data that I want to fit into a LogNormal distribution. I called the parameters of the Lognormal distribution "sigma" and "mju".
To accomplish this, I am trying to create a function in VBA, called "regreslognormalsigma". This function requires the following ranges as input:
- probabilitetet --> this range contains the cumulative density of the argument;
- abshisat --> this range contains the arguments, in my case these are displacements of a structure in a seismic event.
I can not figure out why the output is always the initial value that I set for "sigma", i.e. the output is always 0.0001.
Can anyone help me out? I would like the function to give me the value of sigma for which the data fit best to a lognormal distribution with a standard deviation sigma. In the meantime, I think the algorithm below can be used to find the mean value also (with little modification, which I plan to do once I solve the issue).
The code is below:
Function regreslognormalsigma(probabilitetet As Range, abshisat As Range) As Double
Dim nrqeliza As Double 'this I use to count the range
Dim Yi As Double
Dim xi As Double
Dim Li As Double
Dim i As Double
Dim j As Double
Dim sigma, mju As Double
Dim sigmafinal, mjufinal As Double
Dim shgk, shgkmin, shgkvar As Double ' these are respectively: the sum of errors squared, the minimum sum of errors squared, and an auxiliary value
Dim result As Double
nrqeliza = probabilitetet.Count
sigma = 0.0001 'initial value
mju = 0.0001 'initial value
shgkmin = 1E+300 'I set a large value for the minimum
shgk = 0 'initial value
result = 0
'Li = WorksheetFunction.LogNorm_Dist(xi, sigma, mju, True) --> this is how I take values of the Log Normal distribution
While sigma < 10
While mju < 10
For i = 2 To nrqeliza
Li = WorksheetFunction.LogNorm_Dist(abshisat(i), sigma, mju, True)
shgk = (((probabilitetet(i) - Li)) ^ 2) + shgk
Next i
If shgk < shgkmin Then
shgkvar = shgk
result = sigma
shgkmin = shgkvar
Else: shgkmin = shgkmin
End If
mju = mju + 0.0001
Wend
sigma = sigma + 0.0001
Wend
regreslognormalsigma = result
End Function