Spreadsheet Practice

Lesson 23 of 29, Analysis

RANK, LARGE & SMALL

Who's on top, and by how much.

Pro9 minutes, 3 exercisesRANK.EQLARGESMALL

Watch it done

3 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

RANK.EQ(number, range) returns a value's place in a list: 1 is the largest. LARGE(range, k) returns the k-th largest value; SMALL the k-th smallest.

To get the name that goes with a top value, look it up: XLOOKUP(MAX(B2:B9),B2:B9,A2:A9).

Syntax

=RANK.EQ(B2,$B$2:$B$9)Rank, 1 = highest
=LARGE($B$2:$B$9,E2)k-th largest
=SMALL(B2:B9,1)Smallest
=XLOOKUP(MAX(B2:B9),B2:B9,A2:A9)Name of the top value

Watch out for

  • Lock the range in RANK, or the list shrinks as you fill down.
  • Ties share a rank, and the next rank is skipped.

The exercises

  1. Rank the team. In C2, rank each rep's sales (1 = highest). Fill down to C9.
  2. Top three values. In F2, return the k-th largest sale using the k in E2. Fill down to F4.
  3. Name the leader. In H2, return the name of the rep with the highest sales.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Rank the team
  2. Top three values
  3. Name the leader
Try these functions in the Formula Lab