Please consider supporting this website by buying me a coffee.
Thank you for your consideration!
Updated | October 30, 2024
Average the Largest 3 Values from the Last 5 Values
The following formula returns the average of the largest 3 values from the last 5 values in B3:B12...
=AVERAGE(LARGE(TAKE(FILTER(B3:B12,LEN(B3:B12)>0),-5),{1,2,3}))
For earlier versions of Excel...
=AVERAGE(LARGE(INDEX(B3:B12,LARGE(IF(LEN(B3:B12)>0,ROW(B3:B12)-ROW(B3)+1),5)):B12,{1,2,3}))
Note that the formula needs to be confirmed with CTRL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns 54.33.
Sample Workook: Download