Microsoft Office Tutorials and References

In Depth Information

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

The workbook cumulative sum.xlsx is available at this book's website.

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 AutoFilter 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 con-

sisting 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: