Microsoft Office Tutorials and References

In Depth Information

**Using Arrays in Formulas**

Figure 3-4:

Calculating

the value of

a stock

portfolio using

an array

function.

The syntax is important. Two ranges are entered into the function: One

contains the cells that hold the number of shares, and the other contains the

cells that have the stock prices. These are multiplied in the function by

entering the multiplication operator (*):

{=SUM(B4:B8*C4:C8)}

Ctrl + Shift + Enter had been pressed to turn the whole thing into an array

function. That special keystroke combination is done when finishing the

formula and not before. Note the lack of subtotals (per stock) in cells D4:D8.

Compare Figure 3-4 with Figure 3-3 and you can see the difference.

Use Ctrl + Shift + Enter to turn a formula into an array formula. You must use

the key combination after entering the formula instead of pressing the Enter

key. The key combination takes the place of the press of the Enter key.

Here’s how you use an array with the SUM function:

1. Enter two columns of values.

The two lists must be the same size.