## Explanation

In this example, the goal is to calculate an average of the top 3 quiz scores for each name listed in column B. For reference, column H has a formula that calculates an average of all 4 scores. This is a slightly tricky problem, because it's not obvious how to limit the scores included in the average to only the top 3 scores. The classic solution is to use the AVERAGE function with the LARGE function as explained below.

### LARGE function

The LARGE function is designed to retrieve the "top nth value" from a set of numbers. For a given range, LARGE(range,1) will return the largest value, LARGE(range,2) will return the 2nd largest value, and so on, as seen below:

```
LARGE(range,1) // 1st largest value
LARGE(range,2) // 2nd largest value
LARGE(range,3) // 2nd largest value
```

In this problem, we need to configure LARGE to retrieve the largest 3 values, and the easiest way to do that is to pass an array constant like {1,2,3} into LARGE as the second argument, *k*. Because we are asking for the top 3 values, LARGE will return an array that contains all 3 values:

```
LARGE(range,{1,2,3}) // largest 3 values
```

### LARGE with AVERAGE

In the example shown, the formula in cell I5 is:

`=AVERAGE(LARGE(C5:F5,{1,2,3}))`

Working from the inside out, the LARGE function is configured to extract the top 3 scores from the range C5:F5 like this:

`LARGE(C5:F5,{1,2,3}) // returns {10,10,10}`

Because we have provided the array constant {1,2,3} for the second argument, *k*, LARGE returns the top 3 scores in C5:F5 in an array like this:

```
{10,10,10}
```

This array is returned directly to the AVERAGE function:

```
=AVERAGE({10,10,10}) // returns 10
```

The AVERAGE function then returns the average of these values as a final result. As the formula is copied down, it returns an average of the top 3 scores for each name in the list. Although this set of data contains only 4 scores, the formula will work correctly for any number of scores, as long as there are at least 3.

### Variable n

To make this formula use a variable for **n**, where **n** represents the number of values to include in the average, you can add the SEQUENCE function like this:

`=AVERAGE(LARGE(C5:F5,SEQUENCE(n)))`

To supply a value for **n**, you can use a cell reference like A1 or simply hardcode a number into the formula. The SEQUENCE function dynamically generates a numeric array which is then returned to LARGE as the second argument, and the formula works the same thereafter.

### Legacy Excel

In Legacy Excel, the SEQUENCE function does not exist. The classic solution for creating a numeric array in older versions of Excel is to use the ROW and INDIRECT functions. For example, to generate a numeric array from 1 to 10, you can use a formula like this:

```
=ROW(INDIRECT("1:10")) // returns {1;2;3;4;5;6;7;8;9;10}
```

The INDIRECT function converts the text string "1:10" to the *range* 1:10, which is returned to the ROW function. ROW then returns an array of row numbers like {1;2;3;4;5;6;7;8;9;10}. To make **n** variable, you can concatenate the string "1:" to a cell reference that provides a value for **n**:

`=AVERAGE(LARGE(C5:F5,ROW(INDIRECT("1:"&n))))`

Note that this formula is an array formula and must be entered with control + shift + enter in older versions of Excel.