Here we have some test scores for a group of students. In column F, I want to set up a formula to display the top students by score. Now, I’m going to use the FILTER function, but we’ll need a way to determine the highest score, the second-highest score, and so on, and for this, I’ll use the LARGE function. LARGE returns the nth largest value in a data set. If I give LARGE the scores with 1 for k, I’ll get the top score, 2 for k gives me the second-highest score, 3 gives me the third-highest score, and so on. Back in cell F5, I’ll enter the FILTER function. For array, I want the full set of data. For the include argument, I need to compare all the scores against the 3rd highest score, which I’ll get with the LARGE function. We want to use greater than or equal to (>=), and LARGE with 3 for k. When I enter the formula, FILTER returns the top three scores in a dynamic array. If I like, I can sort the data by Score in descending order with the SORT function Notice these results are dynamic. If I temporarily change a score to 100, we’ll get a new list of top scores. Now that I have the formula working, I’ll replace the hardcoded 3 in LARGE with a reference to cell H2, which already contains the number 3. Now I can change H2 as I like, and FILTER will deliver correct results. The formula for bottom n results is very similar, but instead of the LARGE function, we use the SMALL function. I’ll start by copying the formula in F5 to the clipboard. Then I’ll paste the formula into cell J5, and replace LARGE with SMALL. I’ll also need to use less than or equal to (<=) instead of greater than or equal to (>=). Finally, if I want the lowest score listed first, I need to change sort_order to ascending. When I enter the formula, we get the lowest 3 scores. Since both formulas are linked to the number in H2, when I change H2, both lists automatically update.
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.
