Print

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Average of Consecutive Negative Numbers in a Column

 


 

Average of Consecutive Negative Numbers in column A.

  

Cell C3 formula Average of Consecutive Negative Numbers (ie. if Consecutive more than Once atleast) in Column A. 

 

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

 

Average Consecutive Negative Numbers

 

 

Cell C3 formula: Average of Consecutive Negative Numbers (ie. if Consecutive more than Once atleast) in Column A (A2:A30).  

  

Cell C3 formula: 

  

=AVERAGE(IF((A2:A29<0)*( A3:A30<0), A3:A30), IF(( A2:A29<0)*( A3:A30<0), IF(A1:A28>=0, A2:A29)))   

   

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

 

1) IF((A2:A29<0)*(A3:A30<0),A3:A30): returns an array of negative values and FALSE values - {FALSE; FALSE; FALSE; -2;-3; FALSE; FALSE; FALSE; FALSE;-2; FALSE; FALSE; FALSE; FALSE;-11; FALSE; FALSE; FALSE; -14.2; FALSE; FALSE;…} - if a cell (in A3:A30) value is negative and its preceding cell (A2:A29) is ALSO negative, then values of the succeeding cells are returned (A3:A30). Note: this skips every first negative out of consecutive negatives because its previous cell will be a positive.

 

2) IF((A2:A29<0)*(A3:A30<0),IF(A1:A28>=0,A2:A29)): returns an array of negative values and FALSE values - {FALSE; FALSE; FALSE;-1; FALSE; FALSE; FALSE; FALSE; FALSE;-1; FALSE; FALSE; FALSE; FALSE; -16; FALSE; FALSE; FALSE; -22.56; FALSE;…} - if a cell (in A3:A30) value is negative and its preceding cell (A2:A29) is ALSO negative, and the preceding cell's preceding cell (A1:A28) is zero or positive, then values of the 1st preceding cells are returned (A2:A29). Note: this returns every first negative out of consecutive negatives.

 

Note: In 1) above, the consecutive negatives were determined after SKIPPING the first negative; In 2) each of the FIRST negative of the consecutive negatives was determined. After determining ALL consecutive negatives in 1) & 2), their Average is determined using the AVERAGE function.    

 

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

   

Related Formulas - illustrated in the Ebook of Excel Formulas.