If you would like to see a specific set of values within a given range. For example, if you are a Debtor’s Clerk and want to see what your top five outstanding balances are you can use an Array Formula. An Array Formula is a formula that can perform multiple calculations on one or more items in an array. You can think of an array as a row of values, a column of values, or a combination of rows and columns of values. In this tip we use the ‘Large’ function in an array formula to show the top five values, but they can also be displayed by using Filters, Tables, PivotTables, Pivot Charts and Conditional Formatting.
Note: Download the sample workbook to practice this exercise.
Applies To: Microsoft® Excel® 2007, 2010 and 2013
To illustrate this example we use the screen shot below.
1. Select cells E3 to E7. This set of cells will hold the top five client balances.
2. In the formula bar, enter the following formula: =LARGE(C3:C17,{1;2;3;4;5}
3. Then press Ctrl+Shift+Enter.
4. The top five balances will be displayed.
NOTE:
- To display the bottom five balances, replace the function; LARGE with SMALL in the formula in step 2.
- To display the top ten values add numbers 6-10 separated by a semi colon in the formula in step 2.
