LARGE and SMALL

About this lesson

LARGE returns the k-th largest number in a range. SMALL returns the k-th smallest. So LARGE(range,2) is the second-highest value.

By the end you can

find the second-highest or third-lowest value in a range.
add up a top three, and say what LARGE does with ties.

Practises LARGE SMALL MAX MIN

The idea

MAX answers one question. LARGE answers the same question for any position: the second-highest, the third, the tenth. The second argument is the position. With 1 in it, the function is exactly MAX.

The problem is ties. LARGE ranks entries, not different values. A list with two 26s returns 26 for position 1 and for position 2. If "second highest" has to mean the next different number, LARGE cannot do it alone. The right tool is a sorted list with the duplicates removed, which the Dynamic Arrays module builds.

The mistake to watch for

LARGE ranks entries, not different values. With two reps on 26 deals, LARGE with 1 and LARGE with 2 both return 26. So the "second-highest" figure in a report is the same as the highest. When second highest has to mean the next different number, LARGE cannot do it alone.

Where this comes up again

MAXIFS and MINIFS Summaries and Reports
SORT Dynamic Arrays
A Frequency Table: UNIQUE with COUNTIF Dynamic Arrays
SUBTOTAL: One Function, Nine Aggregates Summaries and Reports

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In E2, find the best deal count with LARGE, asking for the first largest.

To begin, type it exactly:

=LARGE(B2:B9,1)

Row	A	B	D
1	Rep	Deals
2	Ade	14	Best
3	Bea	9	Second best
4	Cal	21	Fewest
5	Dev	17	Top three together
6	Eli	9
7	Fay	26
8	Gus	12
9	Hal	17

Every step

1

In E2, find the best deal count with LARGE, asking for the first largest. To begin, type it exactly: =LARGE(B2:B9,1).

Hint. The range, then 1.
2

In E3, the second-best count.

Hint. The range, then 2.
3

In E4, the fewest deals anybody closed, using SMALL.

Hint. The range, then 1.
4

Two reps closed nine deals. Read =SMALL(B2:B9,2) and say what it returns.

Hint. Sort them: 9, 9, 12, ...
5

In E5, add together the three largest counts, using LARGE three times.

Hint. Positions 1, 2 and 3, added together.