Software

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.

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.

0 comments

Editor's Picks