=DOTPRODUCT(cellreference, cellreference)
DOTPRODUCT is a generalized version of the Excel function SUMPRODUCT, and it is very useful for defining the objective function and constraints of linear programming problems. DOTPRODUCT is also recognized for fast problem setup as described in the “Fast Problem Setup” section in the Frontline Solvers User Guide chapter “Building Large Scale Models”, provided that you follow the rules outlined earlier: Your formula must consist only of =DOTPRODUCT(cell reference, cell reference) where all of the cells in one of the cell references are decision variables, and all of the cells in the other cell reference are constant in the Solver problem. Each cell reference must be either an individual selection or a defined name, but the cell ranges specified by the two arguments need not have the same “shape” (row, column, or rectangular area).
For use in Excel and for purposes of fast problem setup, DOTPRODUCT will accept defined names that specify multiple selections for either of its arguments. For example, if you had designed a model where the decision variables consisted of several rectangular cell selections, you could still calculate the objective function for your model with one call to DOTPRODUCT.
DOTPRODUCT always processes its arguments in column, row, area order – in an individual selection it reads cells across columns, wrapping around to subsequent rows, and in a multiple selection it reads the individual cell selections in the order in which they are listed. For example, the formula:
=DOTPRODUCT(A1:C2,D1:D6)
will calculate as =A1*D1+B1*D2+C1*D3+A2*D4+B2*D5+C2*D6.
The Array Form of DOTPRODUCT
If SUMPRODUCT is used in an array formula, it returns a scalar (single number) result, which is returned in every cell of the array. However, if DOTPRODUCT is used (with the proper arguments) in an array formula, it returns an array result. You can use this capability to calculate the left hand sides of several constraints with a single array formula. In a sparse optimazation model where you’d like to use the built-in function MMULT to compute the constraint values, but the variables and constraints aren’t laid out in a single matrix, you can use the array form of DOTPRODUCT instead.
Further, when you use the array form of DOTPRODUCT, Analytic Solver Pro and Premium Solver Pro will recognize this form and use it to process many constraints at once in problem setup. (The array form is recognized for fast problem setup, and it’s also recognized by the PSI Interpreter in Premium Solver Platform, Risk Solver Platform, and Analytic Solver Platform.) If you can’t use the array form, even the simple form of DOTPRODUCT will save time in problem setup.
DOTPRODUCT will return an array value when the number of cells in one of its arguments is an even multiple of the number of cells in its other argument. As an example, consider the calculation of parts used in the LP model EXAMPLE1. The decision variables are in cells D9 to F9 (3 cells), and the coefficients of the constraint left hand sides – the number of parts used for each product – are in cells D11 to F15 (15= 3*5 cells). We want to calculate the left hand sides of the constraints in cells C11 to C15. To do this, we would first select the group of five cells C11:C15 with the mouse. Then we would type:
=DOTPRODUCT(D9:F9,D11:F15)
completing the entry with CTRL+SHIFT+ENTER instead of just ENTER. The formula will display as {=DOTPRODUCT(D9:F9,D11:F15)} – the braces are added by Microsoft Excel when the formula is array-entered. With the cell values shown in EXAMPLE1 prior to solution (e.g. 100 for each of the decision variables), this array formula will calculate 200 in C11, 100 in C12, 500 in C13, 200 in C14 and 400 in C15. Hence, it will compute the same set of values as the array expression shown earlier: {=MMULT(_A, TRANSPOSE(_X))}.
Whether it is used in the simple form or the array form, DOTPRODUCT always processes its arguments in column, row, area order. In the array form, when the cells in the “shorter” argument have all been processed and cells remain to be processed in the “longer” argument, DOTPRODUCT “wraps around” to the beginning of the “shorter” argument. In the example above, cell C11 calculates the value =D9*D11+E9*E11+F9*F11; cell C12 computes =D9*D12+E9*E12+F9*F12; and so on. Keep this rule in mind when you use the array form of DOTPRODUCT, and keep your spreadsheet layouts as simple as possible!