Microsoft Office Tutorials and References

In Depth Information

Figure 15-8:
Using an array formula to correct rounding errors.

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 calcu-

lates 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-9, the values are stored in a range named Data, and the value of n is in cell

D4 (named n).