Hi - Dave here.
Happy Friday!
The AVERAGE function is very easy to use. Simply give AVERAGE a range, and it returns an average:
=AVERAGE(range)
But what if you want to average just the top 3 values in the range? This is a slightly tricky problem in Excel, because it's not obvious how to limit the values included in the average.
The solution is to combine the AVERAGE function with the LARGE function like this:
=AVERAGE(LARGE(range,{1,2,3}))
The second value given to LARGE, {1,2,3} is called an array constant. Essentially, we are asking LARGE for the top 3 values in the range. You can see this approach in the worksheet below.
[
download the workbook and read the full explanation]
Notice the top 3 average is generally higher than the regular average. You will see the LARGE function appear in formulas that deal with "nth largest values". Click the link above for details and to download the worksheet.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
