# Average of Top N Values in Excel

**AVERAGE **function is one of the most popular functions in Excel. Apply **AVERAGE **together with some other functions, we can calculate average properly based on different situations.

In this article, we will introduce you the way to calculate average of the top N numbers from a range. To approach our goal, except the main **AVERAGE** function, we also apply **LARGE** function.

__EXAMPLE__

__EXAMPLE__

In this case, we want to calculate the average of the top three scores from 5-rounds competitions for each student. To calculate the average of top N values, we need the help of **LARGE** function.

__SOLUTION__

__SOLUTION__

To create a formula to get average in this case, we need to know:

**1)** Find out the top three values by **LARGE** function.

**2)** Calculate average of the top three values by **AVERAGE** function.

The general formula is **=AVERAGE(LARGE(Range,{1,2,…,N}))**.

__FORMULA with AVERAGE & OTHER FUNCTIONS__

__FORMULA with AVERAGE & OTHER FUNCTIONS__

In C2, we input the formula **=AVERAGE(LARGE(B2:F2,{1,2,3}))**. After typing, press **Enter**, then average “98” of the top three scores is returned.

For Kara, five scores are saved in range B2:F2, the top three values are 100, 96 and 98, so the average is (100+96+98)/3=98, the formula returns correct result.

__FUNCTION INTRODUCTION__

__FUNCTION INTRODUCTION__

The main function in this formula is “**AVERAGE**”, it returns the average of the top three values by our demands; “**LARGE**” is used for searching for the top three values among all given values.

** a.**** LARGE function returns the Kth largest number from a given set of numbers or a range reference.**

**Syntax: **

=LARGE(array,k)

* ***Example:**

*//If we input 2 for, only the second top value is returned;*

*//If we want to returning more than one top value, we can set an array constant as the function’s second argument;*

*//If we input {1;2} for k, 1 and 2 are separated by “;”, the top two values are saved in the same column;*

*//If we input {1,2} for k, 1 and 2 are separated by “,”, the top two values are saved in the same row. *

** **

**b. AVERAGE function returns the average of numbers from a given range reference.**

**Syntax: **

=AVERAGE(number1, [number2], …)

* *

**Example:**

*//For arguments “number1, number2,…”, they can be a set of numbers, or an array of numbers like {1;2;3}.*

** **__FORMULA EXPLANATION__

=AVERAGE(LARGE(B2:F2,{1,2,3}))

*//This formula contains 2 functions, we explain functions from inside to outside. *

a. For **LARGE(B2:F2,{1,2,3})**, range B2:F2 provides all scores from 5 rounds competitions; array constant {1,2,3} is the second argument “**k**” value, it determines the count of top numbers returned by **LARGE**.

In this case, B2:F2 is {}, **LARGE **returns below array:

LARGE({100,90,95,96,98},{1,2,3}) -> {100,98,96}

b. Array {100,98,96} from last step goes into **AVERAGE** calculation now.

**=AVERAGE({100,98,96})**

So, we get 98 properly after above two steps.

### Related Functions

- Excel AVERAGE function

The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:**=**AVERAGE (number1,[number2],…)…. - Excel LARGE function

The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…