Page 378 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 335
Performing Magic with Array Formulas
Summing the n largest values in a range
The following array formula returns the sum of the 10 largest values in a range named Data:
{=SUM(LARGE(Data,ROW(INDIRECT(“1:10”))))}
The LARGE function is evaluated 10 times, each time with a different second argument (1, 2, 3, and so on
up to 10). The results of these calculations are stored in a new array, and that array is used as the argument
for the SUM function.
To sum a different number of values, replace the 10 in the argument for the INDIRECT function with
another value.
If the number of cells to sum is contained in cell C17, use the following array formula, which uses the con-
catenation operator (&) to create the range address for the INDIRECT function:
{=SUM(LARGE(Data,ROW(INDIRECT(“1:”&C17))))}
To sum the n smallest values in a range, use the SMALL function instead of the LARGE function.
Computing an average that excludes zeros
Figure 18.2 shows a simple worksheet that calculates average sales. The formula in cell B14 is 18
=AVERAGE(B5:B12)
FIGURE 18.2
The calculated average includes cells that contain a 0.

Two of the sales staff had the week off, however, so including their 0 sales in the calculated average doesn’t
accurately describe the average sales per representative.

NOTE The AVERAGE function ignores blank cells, but it does not ignore cells that contain 0.
NOTE
The following array formula returns the average of the range but excludes the cells containing 0:
{=AVERAGE(IF(B5:B12<>0,B5:B12))}

335