Microsoft Office Tutorials and References

In Depth Information

Figure 14-2:
An array formula to calculate the total sales.

You can enter this formula into any cell. Remember: When you enter this formula, make sure you press

Ctrl+Shift+Enter (and don't type the curly brackets).

This formula works with two arrays, both of which are stored in cells. The first array is stored in B2:B7, and the

second array is stored in C2:C7. The formula multiplies the corresponding values in these two arrays and cre-

ates a new array (which exists only in memory). The new array consists of six values, which can be represented

like this (the reason for using semicolons is explained a bit later):

{150;1000;100;90;180;200}

The SUM function then operates on this new array and returns the sum of its values.

In this case, you can use Excel's SUMPRODUCT function to obtain the same result

without using an array formula:

=SUMPRODUCT(B2:B7,C2:C7)

As you'll see, however, array formulas allow many other types of calculations that are otherwise not pos-

sible.