Search maximum and minimum of a function in a given range and its corresponding value of independent variable
More often, we need find the maximum or minimum value of a function within a given range. In Excel, we can develop a user-defined worksheet function to fulfill this goal. Here provides a user-defined function called minmax. This function is very powerful because it can be applied to any polynomial equation.
The definition of the function is:
MinMax(n As Integer, X As Object, b As Object, Optional isMax As Boolean = True)
Where, n stands for the number of independent variables, X includes the range and step size of each independent variables, isMax is a boolean variable with default value of TRUE for searching maximum and with a value of FALSE for searching minimum.
The return value of the function is an row array. The number of cells is equal to the number of independent variables plus 1. The first returned value is the maximum of minimum of the function and the rest include values of independent variables at this point.
Here lists VBA source code.
Option Explicit
Option Base 1
' search the minimum or maximum value of an equation in a specific range
Function MinMax(n As Integer, X As Object, b As Object, Optional isMax _
As Boolean = True)
' n - the number of variables
' X - is a n-by-3 matrix, first column is the initial values for each variable
' second column is the end values for each variables
' third column is the step size for each variables
' b - n+1-by-n+1 matrix, the coeficients for polynomial equation
' column 1 - constant, column 2 - x1, and so on
' row 1 - constant, row 2 - x1, ans so on
' isMax - a boolean variable to indicate calculate the maximum or minimum
'
' output variable - a row vector with n+1 values
' first value - maximum or minimum value of the equation
' the remaining values - the values for all xs at maximum or minimum of y
'
Dim i, j, l, y
ReDim myMinMax(n + 1)
If isMax Then
myMinMax(1) = -1E+100
Else
myMinMax(1) = 1E+100
End If
Dim xtemp, TotalN
ReDim tempint(n), xn(n), xminmax(n)
TotalN = 1
For i = 1 To n
tempint(i) = 1
Next
For i = 1 To n
xn(i) = Round((X(i, 2) - X(i, 1)) / X(i, 3)) + 1
TotalN = TotalN * xn(i)
Next
For j = 1 To n
For i = j + 1 To n
tempint(j) = tempint(j) * xn(j)
Next
Next
ReDim gridx(TotalN, n)
For i = 1 To TotalN
j = 1
Do While j <= n
gridx(i, j) = X(j, 1) + X(j, 3) * (((i - 1) \ tempint(j)) Mod xn(j))
j = j + 1
Loop
Next
For l = 1 To TotalN
y = 0
For i = 1 To n + 1
For j = i To n + 1
If i = 1 Then
If j = 1 Then
y = y + b(i, j)
Else
y = y + b(i, j) * gridx(l, j - 1)
End If
Else
y = y + b(i, j) * gridx(l, j - 1) * gridx(l, i - 1)
End If
Next
Next
If isMax Then
If y > myMinMax(1) Then
myMinMax(1) = y
For i = 1 To n
myMinMax(i + 1) = gridx(l, i)
Next
End If
Else
If y < myMinMax(1) Then
myMinMax(1) = y
For i = 1 To n
myMinMax(i + 1) = gridx(l, i)
Next
End If
End If
Next
MinMax = myMinMax
End Function
Download sample Excel file - minmax. Please notice that the sample Excel file includes macro. You should enable macro when you open the file. Otherwise, you cannot run the macro in the Excel sheet.
Suppose that we have polynomial like the following,
Y= - 14.8 + 0.232 X1+ 0.159 X2- 0.129 X3- 0.135X1X2- 0.00070 X1X1+ 0.00120 X2X2+ 0.00113 X3X4- 0.00149 X4X4
First we have to rearrange the formula and put the coefficients into a matrix format like the following,
Then arrange the boundary and step size of each independent variable like the following,
The trick part is the return values. You should select the number of cells equal to the number of independent variables plus one within a single row first, enter the following formula,
=minmax(4,B2:D5,B7:F11,FALSE)
Attention now! Then, press down "Ctrl" + "Shift" first and press "Enter" key to make the formula work correctly. This is the method to enter a function returning an array or matrix. Remember, it may take a while to search the maximum or minimum value based on the function and the size of searching boundaries.
I want to say – thank you for this!,