# 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

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

Sub FindDefIntegralAndMinValue()
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
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

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 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").Value

C = Range("B12").Value

Friday, June 19, 2015 2:29 PM