To perform complex calculations, use an Array Formula.
Example: In this example there are two ranges to multiply (each item multiplies Quantity by Price) and totals the results.
1. For Range 1, the Name defined for range B2:B10 is Quantity (see Tip #188).
2. For Range 2, the Name defined for range C2:C10 is Price.
3. In cell D16, enter the following Array Formula:
={SUM(Quantity*Price)}
The formula returns the result of the totals of the Quantity range, multiplied by the Price range.
To create an Array Formula, select the cell after manually entering the formula, press F2, and then press Ctrl+Shift+Enter.
The technical side of an array formula
An Array saves values or strings to temporary memory locations. The Array technique opens as many cells in memory as it needs to keep the multiplied numbers; the Array Formula returns the total (in this example) of these numbers from the temporary memory to the cell.
Screenshot // Array Formulas – the Technical Side
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
