Basic FunctionsIntermediate

RANK Function

Position in the table, worked out rather than eyeballed.

Task:

The quarterly figures are in and the regional director wants the branches placed 1 to 5, best first. The rows are in alphabetical order and are staying that way, so work the position out in column C.

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.

    CoreIntermediate
  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.

6 rows × 3 columns5 cells you fill in
ABC
1BranchQuarterly revenueRank
2Leeds418500
3Cardiff372000
4Glasgow455200
5Bristol389750
6Norwich401300

Functions used here

What this exercise teachesMay contain the answer

Sorting would answer this too, and would destroy the alphabetical order the sheet is circulated in. RANK reports the position while leaving the rows where they are, which is what you want on anything someone else reads. The absolute reference is the part that catches people: without it, row 3 ranks itself against B3:B7 and the numbers come out plausible and wrong.