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.