Excel track · Exercise 15 of 86 Paid

Average functions summary

Join two summary functions into one answer, reading the nesting inside-out.

Intermediate

One team's swim times summarised two ways

Read-only data · screen 1 of 2 · answer in C2

Put the average of the two highest times into C2, taking the values from the unsorted column.

	A	B	C
1	Swimmer	Seconds	Top-two average
2	Ada	58
3	Ben	61
4	Cleo	55
5	Dev	57
6	Eli	50

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Put the place the middle time earns into C2, so the row shows where the median sits.

	A	B	C
1	Swimmer	Seconds	Median's place
2	Ada	58
3	Ben	61
4	Cleo	55
5	Dev	57
6	Eli	50

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

A summary often needs two functions joined together, and Excel has none that averages the top two values on its own. The formula =(LARGE(B2:B6,1)+LARGE(B2:B6,2))/2 asks LARGE twice and divides by two. Read it inside out: each LARGE finds one value, the plus joins them, and the division finishes the job.

The five times are 58, 61, 55, 57 and 50, so LARGE picks 61 first and 58 second, and their average is 59.5. The second screen nests MEDIAN inside RANK: the middle time is 57, and 57 sits third from the top, so the answer is a place and not a time.

A mean of a subset can land on a number that appears nowhere in the data, such as 59.5 here. Two LARGE calls pointing at different ranges average two different groups.

Syntax

=RANK(MEDIAN(range), range) and =(LARGE(range,1)+LARGE(range,2))/2

Worked example

On One team's swim times summarised two ways, =(LARGE(B2:B6,1)+LARGE(B2:B6,2))/2 returns 59.5.

=(LARGE(B2:B6,1)+LARGE(B2:B6,2))/2

Common errors

Error codes for MEDIAN, LARGE and RANK combined
Error	What it means and how to fix it
`#NUM!`	One of the LARGE positions runs past the end of the range, which leaves LARGE no value to add.
`#N/A`	RANK was given a number that is not in the range, which an average of a subset often produces: 59.5 appears nowhere among the times.
`#VALUE!`	An argument in the nest arrived as text that cannot be read as a number.

At work

A selection report that averages a team's top two results, and a review sheet that shows where the median case sits in the ranked list.