How to calculate the average of the top 5 values

Let’s say you have a workbook containing sales over a two year period. You would now like to know what the average is for your top 5 sales values, as well as the value for your largest sale.

In our example, which you are welcome to download here, we are using Column A for the Year, B for the Month and C for the Sales values.

1. To calculate the average of the top 5 sales values, we can use the AVERAGE and LARGE functions: =AVERAGE(LARGE(C2:C27,{1,2,3,4,5}))

2. The LARGE function returns an array of the top 5 sales values.  The AVERAGE function then takes that total and returns the average of those 5 sales values.

3. To return the value of the nth largest sales, use the LARGE function on its own.  If you want the largest sales value, then you would use a 1 in the formula, the second largest value would use a 2, etc. =LARGE(C:C,1)

The above image quickly and efficiently summarises these formulas, and allows you to be more effective when working in Excel.