# How do you find 5 highest values in Excel?

Home Excel Formulas How to Average TOP 5 Values in ExcelLets say you have a line-up of 20 products in your company and you have data for monthly sales of all the 20 products.From

Home Excel Formulas **How to Average TOP 5 Values in Excel**

Lets say you have a line-up of 20 products in your company and you have data for monthly sales of all the 20 products.

From that data, you want to calculate the average of the top 5 products. In short, you want to get the average of the top 5 values from that data. Look at the below example.

## Formula: Average TOP 5 Values

To average the top 5 scores from the list, you can use a formula based on the combination of LARGE and AVERAGE. And, the formula will be:

=AVERAGE(LARGE(B2:B21,{1,2,3,4,5}))

### How this formula works

To understand this formula, you need to split it into two parts. In the first part, we have used the LARGE function.

#### LARGE Function

The LARGE function can return the nth largest value from data. If you specify 2 as the nth value it will return the second-highest value from the data.

Here you need the top 5 values, not just one so that you will average them. For this, you need to enter the below array as nth value.

{1,2,3,4,5}

When you enter this into the function, it returns an array of top 5 values, just like this below.

In the **end**, the average function returns the average of those values. Here you need to understand that the average function can take an array without entering a formula as CTRL + SHIFT + ENTER.

download example file

Excel Formulas

## More Formulas

- Calculate Cube Root
- Calculate Compound Interest in Excel
- Calculate SQUARE ROOT in Excel + Insert Symbol
- Calculate Weighted Average in Excel
- MAX IF
- Quickly Calculate Ratio
- Round a Number to Nearest 1000, 100, and 10
- Round to Nearest .5, 5. 50 (Down-Up)
- Calculate Percentage Variance