Macro to calculate an definite integral F(x) and chosing x for which F(x) is minimum RRS feed

  • 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 .



    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


        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

    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,


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

    C = Range("B12").Value

    Friday, June 19, 2015 2:29 PM