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

Answer 1

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
'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

Answer 2

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

Answer 3

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