How to count consecutive positive/negative numbers in Excel

Counting consecutive negative numbers in an Excel spreadsheet can be a challenge, but with this guide, you’ll learn an effective solution to do it. Follow the practical example to count consecutive negative numbers and optimize your data management.

Are you struggling with an Excel spreadsheet containing both positive and negative numbers? Do you need to calculate the number of consecutive negative numbers? Counting negative numbers in a list might seem simple, but when it comes to consecutive negative numbers, the situation gets a bit more complicated. In this article, I’ll show you an effective solution to count consecutive negative numbers in Excel, illustrating it with a practical example.

Example

Suppose we have a series of data related to the weekly stock market, and we are asked to calculate the number of consecutive weeks the market experienced losses. The data is organized in two columns, with the header in row 1 and data starting from cell A2 and ending in cell B10.

To calculate the number of consecutive losing weeks, enter the following formula in cell C2 and press Ctrl + Shift + Enterto confirm the formula as an array formula:

=MAX(FREQUENCY(IF(B2:B10<0,ROW(B2:B10)),IF(B2:B10>=0,ROW(B2:B10))))

The result will be the number of consecutive weeks the stock market was in the red.

How the Formula Works

The formula uses the “FREQUENCY” function to count the number of times consecutive negative numbers appear. Below, I’ll explain step by step how the formula achieves the desired result.

Step 1: Find the Row Numbers of Values Less Than 0

The “IF(B2:B10<0,ROW(B2:B10))” formula returns an array with the row numbers of values that are less than 0. For example, if the negative numbers are in rows 2, 3, and 4, the array will be {2;3;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.

Step 2: Find the Row Numbers of Values Greater Than or Equal to 0

The “IF(B2:B10>=0,ROW(B2:B10))” formula returns an array with the row numbers of values that are greater than or equal to 0. For example, if the positive numbers are in rows 5, 6, and 7, the array will be {FALSE;FALSE;FALSE;FALSE;5;6;7;FALSE;FALSE}.

Step 3: Calculate the Array of Consecutive Negative Numbers

Using the “FREQUENCY” function, we calculate the array of consecutive negative numbers. The formula will be similar to this: =FREQUENCY({2;3;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{FALSE;FALSE;FALSE;FALSE;5;6;7;FALSE;FALSE})“. The resulting array will be {3;0;0;0}.

Step 4: Find the Maximum of the Array

The final result of the formula will be the maximum value in the array of consecutive negative numbers. In this case, the result will be 3, as there were 3 consecutive weeks of losses in the stock market.

VBA Solution: Custom Function to Count Negative Numbers

In addition to the formula shown earlier, you can use a VBA solution to calculate consecutive negative numbers in Excel. Below is a custom function you can use to achieve the same result.

Function ContaNumeriNegativi(rng As Range)
  
  Dim r As Range
  Dim c As Long
  Dim m As Long
  
  Application.Volatile
  
  c = 0
  m = 0
  
  On Error Resume Next
  
  For Each r In rng.Cells
    If r.Value < 0 Then
      c = c + 1
      If c > m Then
        m = c
      End If
    Else
      c = 0
    End If
  Next r
  
  ContaNumeriNegativi = m
  
End Function

To use this function, enter =ContaNumeriNegativi(B2:B10)” in cell C2. The result will be the same as obtained using the previous formula.

Counting Consecutive Positive Numbers

Similarly, you can calculate the number of times consecutive positive numbers appear. The formula will be almost identical to the one used for negative numbers, but with a slight difference in the signs. The formula will be as follows:

=MAX(FREQUENCY(IF(B2:B10>0,ROW(B2:B10)),IF(B2:B10<=0,ROW(B2:B10))))

Summing Consecutive Negative Values

In the previous example, we calculated the number of consecutive negative numbers. However, if you need to sum the consecutive negative values, you can use the following formula:

=MIN((COUNTIF(OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,C2),"<0")=C2)*SUBTOTAL(9,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,C2)))

Keep in mind that the example provided is for demonstration purposes only, and summing percentage values doesn’t make sense. Counting consecutive positive or negative numbers in an Excel spreadsheet can seem like a complicated task, but by using the correct formulas and functions, you can achieve the desired results. The formula and VBA solution presented in this article will allow you to count consecutive positive or negative numbers efficiently. I hope this information has been useful and can make your work with Excel easier.

Pubblicato in Excel