Excel 2010 Bible

Part II: Working with Formulas and Functions

302

On the CD
This workbook is available on the companion CD-ROM. The file is named cumulative sum.xlsx.

Summing the “top n” values ....................................................................................

In some situations, you may need to sum the n largest values in a range — for example, the top ten

values. If your data resides in a table, you can use autofiltering to hide all but the top n rows and

then display the sum of the visible data in the table’s total row.

Another approach is to sort the range in descending order and then use the SUM function with an

argument consisting of the first n values in the sorted range.

A better solution — which doesn’t require a table or sorting — uses an array formula like this one:

{=SUM(LARGE(Data,{1,2,3,4,5,6,7,8,9,10}))}

This formula sums the ten largest values in a range named Data. To sum the ten smallest values,

use the SMALL function instead of the LARGE function:

{=SUM(SMALL(Data,{1,2,3,4,5,6,7,8,9,10}))}

These formulas use an array constant comprised of the arguments for the LARGE or SMALL func-

tion. If the value of n for your top-n calculation is large, you may prefer to use the following varia-

tion. This formula returns the sum of the top 30 values in the Data range. You can, of course,

substitute a different value for 30.

{=SUM(LARGE(Data,ROW(INDIRECT(“1:30”))))}

Figure 13.13 shows this array formula in use.

FIGURE 13.13

Using an array formula to calculate the sum of the 30 largest values in a range.