Basic FunctionsIntermediate

LARGE and SMALL Functions

Find the nth largest or smallest values.

Task:

You are analyzing a dataset to identify extreme values. Find the 2nd largest value in cell B8 and the 2nd smallest value in cell B9 to understand the range and distribution of your data.

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of LARGE, SMALL

0/3

The same formula in the other shapes it takes at work.

  1. LARGE and SMALL Functions

    Find the nth largest or smallest values.

    CoreIntermediate
  2. A top-three list with LARGE

    Point k at a cell and fill down for 1st, 2nd and 3rd.

    Intermediate
  3. Who came second? LARGE with INDEX/MATCH

    LARGE finds the value; INDEX/MATCH finds whose it is.

    Advanced

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

9 rows × 2 columns2 cells you fill in
AB
1Values
245
323
467
534
689
712
82nd Largest
92nd Smallest

Functions used here

What this exercise teachesMay contain the answer

LARGE and SMALL functions help find specific ranked values (e.g., 1st, 2nd, 3rd largest/smallest) in datasets without needing to sort the entire dataset.