On this webpage, we extend the results about global maximum and minimum from Maxima/Minima of a Function about a continuous function f(x) in one variable to continuous functions f(x, y) and f(x, y, z) in two or three variables.
The following worksheet functions take lambda function parameters. These are described in Real Statistics Lambda Capabilities and Lambda Functions using Cell Formulas.
Worksheet Functions
Real Statistics Functions: The Real Statistics Resource Pack provides the following worksheet functions that identify the global minimum and maximum of a function in two variables x and y.
F2MIN(R1, lox, upx, loy, upy, gx, gy, Rx, Ry): a 1 × 3 row array containing the values x, y, f(x,y) that produces the global minimum of f(x,y) in the ellipse whose x-axis is (lox, upx) and y-axis is (loy, upy) with gx equally-spaced intervals on the x-axis and gy equally-spaced intervals on the y-axis. gx and gy default to 200.
F2MAX(R1, lox, upx, loy, upy, gx, gy, Rx, Ry): a 1 × 3 row array containing the values x, y, f(x,y) that produces the global maximum of f(x,y) in the ellipse whose x-axis is (lox, upx) and y-axis is (loy, upy) with gx equally-spaced intervals on the x-axis and gy equally-spaced intervals on the y-axis. gx and gy default to 200.
Here, R1 and R2 are cells that contain a formula that represents the function f(x,y) and Rx is the address of a cell referenced in R1 (and R2) that points to the variable x. If Rx is omitted, then it defaults to the first cell referenced in R1. Similarly, Ry is the address of a cell referenced in R1 (and R2) that points to the variable y. If Ry is omitted, then it defaults to the second cell referenced in R1.
Three-variable functions
F3MIN(R1, lox, upx, loy, upy, loz, upz, gx, gy, gz, Rx, Ry, Rz): a 1 × 4 row array containing the values x, y, f(x,y) that produces the global minimum of f(x,y) in the hyper-ellipse whose x-axis is (lox, upx), y-axis is (loy, upy) and z-axis is (loz, upz) with gx, gy and gz equally-spaced intervals on the x-axis, y-axis and z-axis. gx , gy and gz default to 50.
F3MAX(R1, lox, upx, loy, upy, loz, upz, gx, gy, gz, Rx, Ry, Rz): a 1 × 4 row array containing the values x, y, f(x,y) that produces the global maximum of f(x,y) in the hyper-ellipse whose x-axis is (lox, upx), y-axis is (loy, upy) and z-axis is (loz, upz) with gx, gy and gz equally-spaced intervals on the x-axis, y-axis and z-axis. gx , gy and gz default to 50.
Example of Global Minimum/Maximum
Example 1: Use the FMIN2 and FMAX2 functions to find the global minima and maxima for the function f(x,y) = 3x – y3 +ey +2 for various ellipses, as shown in Figure 1.
Figure 1 – Global maximum/minimum for f(x,y)
The function f(x,y) is specified in cell G2 using the formula shown in cell I2. We see, for example, that the minimum of this function in the ellipse with -1 ≤ x ≤ 1 and -1 ≤ y ≤ 1 occurs when x = -1 and y = -.46. For these values of x and y, f(x,y) = -0.27138, as shown in row 5 of the figure. Here, the array formula shown in cell I5 is used to calculate the values in range E5:G5.
The corresponding maximum occurs when x = 1, y = .91, and f(x,y) = 6.730752, as shown in row 13.
Optional forms
Note that we could specify the addresses of the x and y variables used for the formula in cell G2, instead of using the defaults, as done above. In this case, we would use the following array formula in range E5:G5.
=F2MIN($G$2,A5,B5,C5,D5,,,E2,F2)
We would get the same result if we use the array formula =F2MIN($G$2,A5,B5,C5,D5,,,,F2) or =F2MIN($G$2,A5,B5,C5,D5,,,E2).
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.
Reference
Wikipedia (2021) Maxima and minima
https://en.wikipedia.org/wiki/Maxima_and_minima