Q Is there any convenient way to add up the largest n values in a range? For instance, how would I do this to compute the total sales volume of my company's top four salespeople?
- Stephen Dobrow
A That depends on which spreadsheet you're using, how your data is arranged, and what you'd care to call "convenient". I've come up with three different ways of doing it: a general method that works for any current Windows spreadsheet; a rather tricky approach that works well for 1-2-3 releases 4 and 5, as long as the data is arranged in a single-column list; and an elegant array formula for Excel.
Figure 1 illustrates the 1-2-3 solution. The formula in cell B2, shown summing the largest four values in the range A5..A15, is @dsum(A4..A15,0,+A5>= @large($A$4..$A$15,$B$1)). This formula employs the database function @dsum, which adds numbers from a list that satisfy a specified criterion. The criterion, in turn, uses the @large function to determine how many numbers to sum.
Note that this setup requires that the values be arranged as a database. They must be in a single-column range with an arbitrary label (in this case in cell A4), to serve as a field name, even though that name doesn't appear anywhere in the formula. Also note that the @large function requires absolute cell addresses.
If the list is arranged horizontally, however, the @dsum trick won't work. Figure 2 shows an alternative solution that you can use with 1-2-3 releases 4 and 5, Quattro Pro 5 and 6, and Excel 4 and 5. The data is shown in range B4..L4.
A series of @if formulas in range B5..L5 picks out the four largest values from the list; the formula in cell B5 is @if(@rank(B4,$B$4..$L$4)<=$B$1,B4,0). (If you set up the example, enter the formula in cell B5 and copy it to range C5..L5). In this formula, the @rank function returns the rank order of cell B4 in the list. The @if function returns B4 if that rank order is higher than that of the index in cell B1; otherwise @if returns zero. The formula in cell B2 is @sum(B5..L5).
Excel's array formulas allow you to collapse the last two formulas into a single cell. With the list of values in range B4:L4 and a 4 in cell B1, the formula {=sum((rank(B4:L4,B4:L4)<=B1)*B4:L4)} gives the sum of the four largest values in the list.
Type in the formula without the curly brackets, then press <Ctrl>+<Shift>+<Enter> to enter it as an array. In this formula, the rank function computes an array of rankings; comparing these with B1 yields an array of true or false values. Multiply the resulting array by the original list to get an array of values in the top four. The sum function totals them up.
Richard Scoville