Find the top 10 values in an Excel range without sorting

When you want to total the
amount of sales from your top 10 performers, you usually would have to sort
the data first, then use the SUM function to add up
the first 10 values in the sorted range. With Excel’s Array function, you can get the same results in a single step.

Suppose you have
a worksheet that lists the names of 30 salespersons in column A and the
amount they sold for the month of February in Column B. To find the total
amount of sales turned in by your top 10 performers without having to sort the
list, follow these steps:

1. Select the range in column B containing Sales
   data for each person named in column A.
2. Click in the Name box in the Formatting toolbar
   and enter SalesData.
3. Enter the following formula in a cell outside the
   named range (for example, D2):
   =SUM(LARGE(SalesData,{1,2,3,4,5,6,7,8,9,10}))
4. Press [Ctrl][Shift][Enter]

You can also find the total
amount of sales for your 10 lowest performers by entering:

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

The
same technique can be used to find the average of your top 10 or lowest 10
performers. For example, if you wish to find the average score of the top 10
students in your class, you would name the range containing their scores TestScores and enter the following formula in a cell
outside the range:

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

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.