QUADPRODUCT Function

QUADPRODUCT is an add-in function, similar to DOTPRODUCT, which allows you to compute a quadratic function in one step. A common use of QUADPRODUCT is in the construction of efficient portfolios, often called portfolio optimization, where the objective is to minimize the total portfolio variance (a measure of riskiness), subject to a threshold on the expected portfolio return. The portfolio variance is a quadratic function of the decision variables (the amounts allocated to each security in the portfolio).

If you sign up (it's free) for our Private Web for Solver Users, you can download QUADPRODUCT and use this function in your own models -- even if you don't have one of our enhanced solvers!

A quadratic function is a sum of terms, where each term is a (positive or negative) constant (called a coefficient) multiplied by a single variable or the product of two variables. This means that in order to represent the most general quadratic function, we might have a coefficient for each instance of a single variable, and a coefficient for each possible pair of two variables. The QUADPRODUCT function is designed to supply such coefficients in a manner similar to SUMPRODUCT and DOTPRODUCT.

You supply the arguments of QUADPRODUCT as shown below:

=QUADPRODUCT(variable cells, single coefficients, pair coefficients)

The second argument supplies the coefficients to be multiplied by each single variable in the first argument, using an element-by-element correspondence. The third argument supplies the coefficients to be multiplied by each pair of variables drawn from the first argument; hence if there are N cells in the first argument, there must be N*N cells in the third argument. The pairs are enumerated starting with the first cell paired with itself, then the first cell paired with the second cell, and so on. For example, if the first argument consisted of the cells A1:A3, there should be nine cells in the third argument, and the values in those cells will be multiplied by the following pairs in order: A1*A1, A1*A2, A1*A3, A2*A1, A2*A2, A2*A3, A3*A1, A3*A2, and A3*A3. The value returned by QUADPRODUCT is the sum of all of the coefficients multiplied by their corresponding single variables or pairs of variables.

When used with the Solver, the first argument should consist entirely of decision variable (changing) cells. The second and third arguments should consist entirely of cells whose values are constant in the optimization problem; if these cells contain formulas, those formulas must not refer to any of the decision variables. For example, in a portfolio optimization problem, the decision variables are the amounts to be allocated to each security; cells in the second argument would all be zero, since no linear terms are needed; and cells in the third argument would contain the covariance of price changes of each pair of securities.

Each of the arguments may be a multiple selection: If an argument consists of more than one area or block of cells, the areas must be separated by commas and the whole argument must be enclosed in parentheses.

Copyright © 1996 Frontline Systems Inc.
Last modified: January 19, 1997