Global maximum/minimum of a multivariate function

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 (lozupz) 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 (lozupz) 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.

Global maximum and minimum

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

Leave a Comment