User Rating: 5 / 5     Average of Largest 5 Values in a Column, per Matching Id

Average of Largest 5 Values in column B, corresponding to the respective Id in column A.

Cell F2 formula Average of Largest 5 Values in column B, corresponding to the respective Id in column A - Blank cells are ignored, and zeros are considered for Average.

-----------------------------------------------------------------------------------------------

Cell F2 formula: Average of Largest 5 Values in column B, corresponding to the respective Id in column A - Blank cells are ignored, and zeros are considered for Average.

Cell F2 formula:

=AVERAGE(IFERROR(LARGE(IF(( A\$2:A\$30=D2)*( B\$2:B\$30<>""), B\$2:B\$30), ROW(A\$1:INDEX( A\$1:A\$30, D\$7))),""))

Explanation - Cell F2 formula (Array Formula - Ctrl+Shift+Enter):

1) IF((A\$2:A\$30=D2)*( B\$2:B\$30<>""), B\$2:B\$30): returns an array of values from non-blank cells of column B, where corresponding cells in column A = D2 - {22.15; 14; 14.9; 11; 4.8; 9.95; FALSE; FALSE;…}.

2) ROW(A\$1:INDEX( A\$1:A\$30, D\$7)): returns an array of numbers from 1 to 5 - {1; 2;3; 4;5}. This is the k argument of the LARGE function and returns the largest 5 values from the array of values in 1) above - {22.15; 14.9; 14;11; 9.95}. Where the total values in the array are less than 5 the LARGE function will return an error for the number of values less than 5 and using the IFERROR function returns blanks for these error values. Using AVERAGE returns the average of the largest 5 column B values where corresponding cells in column A = D2.

-----------------------------------------------------------------------------

Related Formulas - illustrated in the Ebook of Excel Formulas.

Other formulas:

Cell E2 formula: Average of Largest 5 Values in column B, corresponding to the respective Id in column A - Blank cells are ignored, and zeros are considered for Average. This is similar to cell F2 formula illustrated above, except that cell E2 uses the hard-coded number 5 for "5 Largest" while cell F2 formula uses a cell reference (ie. cell D7). Array formula (Ctrl+Shift+Enter).

Cell G2 formula: Cells G2 formula returns the Average of Largest 5 Values in column B, corresponding to the respective Id in column A. This formula considers Largest 5 values, corresponding to cell D2 Id in column A, but if there are multiple occurrences of 5th Largest value then all these occurrences are considered (ex. for Id "C", 6 values are considered). This uses the AVERAGEIFS function. Array formula (Ctrl+Shift+Enter).

Cell H2 formula: Cells G2 formula returns the Average of Largest 5 Values in column B, corresponding to the respective Id in column A. This is similar to cell G2 formula above, without using the AVERAGEIFS function. Array formula (Ctrl+Shift+Enter).