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).