Page 384 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 341
Performing Magic with Array Formulas
You also can eliminate these types of rounding errors by using the ROUND function in the formula that cal-
culates 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 18.6, the values are stored in a range named Data, and the value of n is in cell D2
NOTE
NOTE
(named n).
FIGURE 18.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: 18
{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.
This formula generates an array of consecutive integers, and the MOD function uses this array as its first
argument. The second argument for the MOD function is the value of n. The MOD function creates another
array that consists of the remainders when each row number is divided by n. When the array item is 0 (that
is, the row is evenly divisible by n), the corresponding item in the Data range will be included in the sum.
You find that this formula fails when n is 0 (that is, when it sums no items). The modified array formula
that follows uses an IF function to handle this case:
{=IF(n=0,0,SUM(IF(MOD(ROW(INDIRECT(“1:”&COUNT(data)))-
1,n)=0,data,””)))}

341