Microsoft Office Tutorials and References
In Depth Information
Figure 15-9: An array formula returns the sum of every n th value in the range.
The following array formula returns the sum of every n th 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.
This formula generates an array of consecutive integers, and the MOD function uses this array as its first argu-
ment. 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 minus 1 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:
Search JabSto ::




Custom Search