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.