Basic Functions
Intermediate

RANK where lowest is best

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

Task:

You are ranking energy suppliers by annual cost, and the cheapest should be number 1. In C2:C7 give each supplier's rank.

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
1SupplierAnnual costRank
2Brightgrid1340
3Coastal Power1185
4Dynamo1410
5Ecoflow1220
6Fieldline1095
7Gridwise1275
What this exercise teachesMay contain the answer

Without the 1, the dearest supplier would be ranked first, which is the opposite of the question. Times, costs, error rates and days late all rank ascending; sales, scores and margins rank descending.