Asked by:
Macro to calculate an definite integral F(x) and chosing x for which F(x) is minimum

Question
-
Dear All,
I wish to calculate an integral function e.e F(x) = f(x) + constant , (where f(x) is an integral equation) over limits of a to b (both positive real numbers). From all the values of F(x), I need the value of x for which F(x) is minimum. I know its sounds tangled but e.g.
f(x) = A. B/((1+(A*x)^C)(1/C), A, B and C are constants
I need your help in a macro which
calculates F(x) over an interval of a to b in say 50 interval and then identifies value of x for which F(x) is minimum. and gives this value of x as output
My apologies if I haven't put it in simpler manner. I have been trying to this by first calculating F(x) over many rows using a Simpsons Method macro and then choosing the desired value of x using VLOOK but its very time consuming and level of accuracy is also. I shall be most happy if i get any suggestion and inputs .
Cheers,
Swapnil
Thursday, June 18, 2015 2:50 PM
All replies
-
Try it like this - you need to first define your function properly and set the constants (see function definition, below), since
Sub FindDefIntegralAndMinValue()
f(x) = A. B/((1+(A*x)^C)(1/C), A, B and C are constants
is not a valid function - not enough parens, and what A .B means is anybody's guess
Dim x As Double
Dim x1 As Double
Dim x2 As Double
Dim val As Double
Dim xMin As Double
Dim fMin As Double
Dim xLo As Double
Dim xHi As Double
Dim iSteps As Integer
Dim dStep As Double
Dim delta As Double
Dim dSum As Double
'Set the parameters
delta = 0.000001
iSteps = 50
xLo = 0.1
xHi = 10
fMin = F(xLo) + 10
x1 = xLo
x2 = xHi
dStep = (x2 - x1) / iSteps
For x = x1 To x2 Step dStep
dSum = dSum + F(x) * dStep
Next x
FindMin:
dStep = (x2 - x1) / iSteps
For x = x1 To x2 Step dStep
val = F(x)
If val < fMin Then
fMin = val
xMin = x
End If
Next x
If Abs(fMin - F(xMin - dStep)) > delta Or Abs(fMin - F(xMin + dStep)) > delta Then
x1 = Application.Max(xLo, xMin - dStep)
x2 = Application.Min(xHi, xMin + dStep)
GoTo FindMin
End If
MsgBox "The integral was " & dSum & " and the min value was " & fMin & " found at " & xMin
End Sub
Function F(x As Double) As Double
'Define your function
Dim A As Double
Dim B As Double
Dim C As Double
A = 10
B = -3
C = 0.01
F = (A + B / 10) / ((1 + (A * x) ^ C) * (1 / C))
End Function
- Edited by Bernie Deitrick, Excel MVP 2000-2010 Thursday, June 18, 2015 5:36 PM
Thursday, June 18, 2015 5:14 PM -
Thanks Bernie,
The function as typed was my error. The code that you have written executes well. While going through the code i realized the virtues of stating the problem precisely. I sincerely appreciate the time you have put here.
To understand the code better, I have a few supplementary questions regarding the code
1. what does 10 signifies in "fMin = F(xLo) + 10"? does it correspond to the constant in "F(x) = f(x) + constant"
2. How can I refer to a cell instead of specifying "10" here e.g cell "A20", as i have to integrate the function over many lines (iterations) and "10" will change for each line (A20, B20,.....). I want to iterate using different constants as in "10" till the xMin value is same of successive iterations
3. Do not we have to set an initial values for dsum while declaring parameters?
4. How can we get the output values of "dSum", "fMin" and "xMin" in separate cells in a row instead of getting them as message box?
5. Also can we input the constants by referring to specific cells in the excel sheet?
With regards,
Swapnil
- Edited by Swaps_excel Friday, June 19, 2015 7:08 AM Addition to text
Friday, June 19, 2015 6:55 AM -
1) I just wanted to set a value for the initial value of the minimum that was greater than an actual value. I could have tested fMin for being 0 (the default value for a number variable), but 0 is a valid value, so I wanted something else...
2) replace 10 with Range("A20").Value
2A) replace 10 with rngV.Value within this loop
For Each rngV In Range("A20:Z20")
Next rngV
3) The default for dSum is 0, which is what I want to start with anyway, so you don't need to initialize it unless you loop multiple values - then you need to re-set it to 0 to clear out the old sum
4) To output the values, use code like
Range("A10").Value = dSum
Range("A11").Value = fMin
Range("A12").Value = xMin
5) To get the constants, use code like
A = Range("B10").Value
B = Range("B11").ValueC = Range("B12").Value
Friday, June 19, 2015 2:29 PM