DOTPRODUCT Function

DOTPRODUCT is a generalized form of SUMPRODUCT, which is standard in Microsoft Excel. Both functions take two arguments and compute the same value: The "inner product" or "dot product": a1 * x1 + a2 * x2 + ... + an * xn where the ais are the cells in one of the arguments, and the xis are the cells in the other argument. But SUMPRODUCT is limited to arguments which are simple selections (not multiple selections) of the same orientation ( a column or a row), and it cannot be used in array formulas. DOTPRODUCT accepts multiple selections for both arguments, and it can be used in array formulas.

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

A simple selection in Microsoft Excel is a cell range such as A1:A10 or C6:D10. A multiple selection is a list of simple selections, such as A1:A10,B1:B5,C6:D10. Many Solver models use a multiple selection for the decision variables, in the By Changing Cells box of the Solver Parameters dialog. Note that when used as an argument to a function, a multiple selection must be enclosed in parentheses. So for example, in the following two cases DOTPRODUCT could be used to compute the desired value, where SUMPRODUCT could not:

=DOTPRODUCT(A1:A10,C6:D10)

=DOTPRODUCT((A1:A10,B1:B5),C6:E10)

Array Formulas and DOTPRODUCT

An array formula in Microsoft Excel is a formula which computes many values at once. To enter an array formula, you select a block of cells, type the formula, and then type CTRL + SHIFT + ENTER to enter the single formula in all of the cells. The formula appears enclosed in {braces}, and it must be edited as a whole -- you cannot change individual cells in the block. Each cell in the block receives one value from the array result. For example, if in cells A1:A10 you enter {=B1:B10+C1:C10}, cell A1 will contain =B1+C1, cell A2 will contain =B2+C2, and so on. Compared to simply copying formulas to other cells, array formulas are faster to recalculate and take less space; they more clearly express your intentions, and are less subject to undetected, accidental modifications. For more information on array formulas, please consult your Microsoft Excel documentation, Help, or the Microsoft Corp. Excel Website.

The array form of DOTPRODUCT allows you to compute the left hand sides of many constraints at once. For example, suppose you are assembling three different products from five components, where each product requires a different set of components (as in the Product Mix sheet of the SOLVSAMP.XLS workbook included with Microsoft Excel). Assume that the number of products to assemble (the decision variables) are in cells D9:F9, and that the components needed for each product are listed in the columns below: D11:D15, E11:E15, and F11:F15. Then you could compute the total amounts needed of each component by array-entering the following formula in cells C11:C15:

{=DOTPRODUCT(D9:F9,D11:F15)}

DOTPRODUCT returns an array result whenever the number of cells on one of its arguments is an even multiple of the number of cells in the other argument. If you are familiar with matrix multiplication and Microsoft Excel's MATMULT function, you will see that the array form of DOTPRODUCT is a generalized version of MATMULT, where the arguments can be multiple selections.

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