Count Groups of Consecutive Positive or Negative or Zero Values
Count Groups of Consecutive Positive Values
Column C formulas Count Groups of Consecutive Positive / Negative / Zero Values; Average (or Maximum) Number of Cells in Positive / Negative / Zero Value Groups.
Cell C3 Formula: Count Groups of Consecutive Positive Values in column A.
Column A contains Positive, Negative & Zero values – determine number of times there are consecutive Positive (or Negative or Zero values) values.
If consecutive cells have Positive Values (ie. there are minimum number of 2 consecutive cells) then it consitutes one group, for example, cell A2 & A3 both have positive values and is counted as 1 group, cell A14 has a positive value but cell A13 & A15 do not have positive values hence A14 is not counted.
———————————————————————————————–
Cell C3 Formula Counts Groups of Consecutive Positive Values in column A.
Cell C3 formula:
=SUM(–(FREQUENCY( IF(A2:A25>0, ROW(A2:A25)), IF(A2:A25<=0, ROW( A2:A25))) >1))
Explanation – Cell C3 formula (Array Formula – Ctrl+Shift+Enter):
1) IF(A2:A25<=0, ROW(A2:A25)): returns an array of values – {FALSE; FALSE; 4;5;6; FALSE; FALSE; FALSE; 10;11; 12;13; FALSE; 15;16; 17;18; 19;20; 21; 22; 23; 24;25} – the numbers indicate row numbers where cell value is equal to or less than zero. For example, the number 4 indicates that cell A4 value is zero or less ie. -8. Cells values greater than zero return FALSE. Note that blank cells are also equated to zero here. This is the bins_array argument of the FREQUENCY function against which the data_array values are grouped to determine their occurrence and frequency.
2) IF(A2:A25>0, ROW(A2:A25)): returns an array of values – {2;3; FALSE; FALSE; FALSE; 7;8;9; FALSE; FALSE; FALSE; FALSE; 14;FALSE; FALSE; …} – the numbers indicate row numbers where cell value is greater than zero ie. positive. For example, the number 2 indicates that cell A2 value is positive ie. 11. This is the data_array argument of the FREQUENCY function.
3) FREQUENCY( IF(A2:A25>0, ROW(A2:A25)), IF(A2:A25<=0, ROW(A2:A25))): returns an array of numbers and zero values – {2;0; 0;3; 0;0; 0;1;0; 0;0; 0; 0;0; 0; 0;0; 0;0}. Using the FREQUENCY function with the data_array argument in 2) above, and the bins_array argument in 1) above, returns the number of occurrences of data_array numbers as grouped by bins_array numbers, for example the 1st number of the bins_array of 4 groups all data_array numbers which are less than or equal to 4, which are 2 & 3 and counts them and returns 2 (this is the frequency or occurrences of data_array numbers which are less than or equal to 4). The next number of 3 in the above array is with reference to the number 10 of the bins_array and indicates that there are 3 numbers in data_array which are less than or equal to 10 and greater than the previous number 4 (ie. 7, 8 & 9). Note that only the 1st instance of each number of bins_array returns the frequency. This means that frequency is returned for each unique number of bins_array argument (which is the 1st instance of each group of consecutive numbers of bins_array – 4, 10 & 15).
4) The FREQUENCY function in 3) above returns an array of numbers and zero values. These are the occurrences of continuous positive numbers in column A, and determining the FREQUENCY function with “>1” returns TRUE and FALSE values where TRUE is returned for array values which are greater than 1 ie. where there is more than 1 consecutive occurrence of positive values. Using a double negation converts TRUE values to 1s and FALSE values to zeros, and using SUM returns the number of groups of consecutive occurrences of positive values.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Column C formulas Count Groups of Consecutive Positive / Negative / Zero Values; Average (or Maximum) Number of Cells in Positive / Negative / Zero Value Groups.
Cell C4 Formula: Counts Groups of Consecutive Negative Values in column A – Array formula (Ctrl+Shift+ Enter).
Cell C5 Formula: Counts Groups of Consecutive Zero Values in column A – Array formula (Ctrl+Shift+ Enter).
Cell C7 Formula: Returns Maximum Number of Consecutive Positive Values in column A – Array formula (Ctrl+Shift+ Enter).
Cell C8 Formula: Returns Maximum Number of Consecutive Negative Values in column A – Array formula (Ctrl+Shift+ Enter).
Cell C9 Formula: Returns Maximum Number of Consecutive Zero Values in column A – Array formula (Ctrl+Shift+ Enter).
Cell C11 Formula: Returns Average Number of Cells in Positive Value Groups in column A – Array formula (Ctrl+Shift+ Enter).
Cell C12 Formula: Returns Average Number of Cells in Negative Value Groups in column A – Array formula (Ctrl+Shift+ Enter).
Cell C13 Formula: Returns Average Number of Cells in Zero Value Groups in column A – Array formula (Ctrl+Shift+ Enter).