Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

The following array formula creates a new array that consists of values in column E, rounded to

two decimal places:

{=SUM(ROUND(E4:E6,2))}

This formula returns $168.31.

You also can eliminate these types of rounding errors by using the ROUND function in the

formula that calculates each row total in column E (which does not require an array formula).

Summing every
nth value in a range

Suppose that you have a range of values and you want to compute the sum of every third value

in the list — the first, the fourth, the seventh, and so on. One solution is to hard-code the cell

addresses in a formula. But a better solution is to use an array formula.

In Figure 15-6, the values are stored in a range named
Data
, and the value of
n is in cell

D4 (named
n).

Figure 15-6:
An array formula returns the sum of every
nth value in the range.

The following array formula returns the sum of every
nth value in the range:

{=SUM(IF(MOD(ROW(INDIRECT(“1:”&COUNT(Data)))–1,n)=0,Data,””))}

This formula returns 70, which is the sum of every third value in the range.