User Rating: 5 / 5     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.

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

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.