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.
 
Search JabSto ::




Custom Search