Microsoft Office Tutorials and References
In Depth Information
Summing the “top n” values
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 autofi ltering 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 fi rst 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
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. Figure 17.13 shows this array formula in
use.
{=SUM(LARGE(Data,ROW(INDIRECT("1:30"))))}
FIGURE 17.13
Using an array formula to calculate the sum of the 30 largest values in a range
Search JabSto ::




Custom Search