User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Count Groups of Consecutive Positive or Negative or Zero Values

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

Count Groups of Consecutive Positive / Negative / Zero Values; Average (or Maximum) Number of Cells in Positive / Negative / Zero Value Groups

 

 

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: Count Groups of Consecutive Positive Values in column A.

 

Cell C4: Count Groups of Consecutive Negative Values in column A.

 

Cell C5: Count Groups of Consecutive Zero Values in column A.

 

Cell C7: Maximum Number of Consecutive Positive Values in column A.

 

Cell C8: Maximum Number of Consecutive Negative Values in column A.

 

Cell C9: Maximum Number of Consecutive Zero Values in column A.

 

Cell C11: Average Number of Cells in Positive Value Groups in column A.

 

Cell C12: Average Number of Cells in Negative Value Groups in column A.

 

Cell C13: Average Number of Cells in Zero Value Groups 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 having Positive Values (ie. 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.

 

Cells C3, C4 & C5 return the number of Groups having a minimum of 2 consecutive Positive, Negative & Zero Values respectively.

 

Cells C7, C8 & C9 return the Maximum Number of Consecutive Positive, Negative & Zero Values respectively.

 

Cells C11, C12 & C13 return the Average Number of Cells in Positive, Negative & Zero Value Groups respectively.

 

Note: All formulas are Array formula (Ctrl+ Shift+ Enter).

 

 

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

 

Cell C3 formula Counts Groups of Consecutive Positive Values in column A.

 

 

Count Groups of Consecutive Positives 

  

"Open Image in New Tab" for a full and clear view. 

 

 

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.

 

 

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

 

   

 

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.