Print

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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. 

 

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

 

Average Largest 5 Values, per Matching Id  

 

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