Microsoft Office Tutorials and References

In Depth Information

**Summing Formulas**

Figure 7-14 shows this formula at work.

Figure 7-14:
Using an IF function to hide cumulative sums for missing data.

The workbook
cumulative sum.xlsx
is available on the companion CD-ROM.

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 comprising the arguments for the LARGE or SMALL

function. If the value of
n for your
n calculation is large, you may prefer to use the following

variation. 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”))))}