Top and bottom values with LARGE and SMALL

1 / 4

Find the 3 lowest sales. The places 1, 2 and 3 are already in D2:D4.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABCDEF
1SalespersonSalesPlaceLowest sales
2Tariq9501
3Nadia56502
4Daniel27253
5Quinn7425
6Jamal3950
7Isla5600
8Kofi5525
9Yusuf3175
10Sam1575
11Rosa4975
12
13
14
15
sales
lowest sales
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How LARGE and SMALL works

  • What do LARGE and SMALL do?

    MAX gives the highest number and MIN the lowest. LARGE and SMALL go further: you choose which place you want.

    • =LARGE(B2:B11,2) gives the second highest.
    • =SMALL(B2:B11,3) gives the third lowest.

  • What is the second part?

    The place, counting from the top for LARGE or from the bottom for SMALL. 1 means the highest (or lowest), so LARGE(B2:B11,1) is the same as MAX(B2:B11).

  • How do you make a top 3?

    Type 1, 2 and 3 in a column and point LARGE at them: =LARGE($B$2:$B$11,D2). Lock the range with $ so it stays put when you copy the formula down; D2 changes to D3 and D4 by itself.

  • What about ties?

    If two people have the same top figure, LARGE gives it twice: in first and in second place.

Know someone fighting with a spreadsheet? Send them this page.