As the formula is copied across and down the table, it returns the top 3 scores for each student in the list. For example we can use LARGE to get the top 3 scores for Hannah like this: In the example shown, the formula in I5 looks like this: This is a clever use of mixed references that takes advantage of the numbers 1,2, and 3 already in the range I5:K5, so that they can be plugged into the formula for n:
The value given for array is the mixed reference $C5:$G5. Notice columns are locked, but rows are not. This allows the rows to update as the formula is copied down, but prevents columns from changing as the formula is copied across. The value given for k (n) is another mixed reference, I$4. Here, the row is locked so that it will not change as the formula is copied down. However, the column is not locked, allowing it to change as the formula is copied across.
Note: use the SMALL function to get the nth smallest value.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
