Basic Functions
Advanced

RANK without shared places

A running COUNTIF gives tied values distinct ranks in the order they appear.

Task:

Prize places must be unique, but two pairs of entrants tied. In C2:C7 give each entrant a distinct place: highest score first, ties broken in favour of whoever is listed first.

Interactive Spreadsheet

Read about RANK

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 RANK

0/3

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

  1. RANK Function

    Position in the table, worked out rather than eyeballed.

    Core
    Intermediate
  2. RANK where lowest is best

    The third argument flips the order, so the smallest value ranks 1.

    Intermediate
  3. RANK without shared places

    A running COUNTIF gives tied values distinct ranks in the order they appear.

    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.

7 rows × 3 columns6 cells you fill in
ABC
1EntrantScorePlace
2Ana88
3Ben92
4Cleo88
5Dev75
6Eli92
7Fay81
What this exercise teachesMay contain the answer

The expanding COUNTIF is 1 the first time a score appears and 2 the second, so the second of a tied pair is pushed one place down. Every place from 1 to 6 is used exactly once, which is what a lookup on place — "who came 3rd?" — needs.