How to Count Consecutive Positive/Negative Numbers in Excel

Counting consecutive negative numbers in an Excel spreadsheet can be a challenge, but this guide will teach you an effective solution. Follow the practical example to count consecutive negative numbers and optimize your data management.

You are struggling with a spreadsheet in Excel A list containing both positive and negative numbers? Need to calculate the number of consecutive negative numbers? Counting negative numbers in a list may seem simple, but when it comes to consecutive negative numbers, things get a little more complicated. In this article, I'll show you an effective solution for counting consecutive negative numbers in Excel, illustrating it with a practical example.

Example

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

To calculate the number of consecutive weeks with losses, enter the following formula in cell C2 and press Ctrl Shift Enter to confirm the formula as an array formula:

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

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

How the Formula Works

The formula uses the function FREQUENCY To calculate the number of consecutive negative numbers. Below, I'll explain step-by-step how the formula returns the desired result.

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

The formula IF(B2:B10 restituisce un array con i numeri di riga dei valori che sono inferiori a 0. Ad esempio, se i numeri negativi si trovano nelle righe 2, 3 e 4, l’array sarà {2;3;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.

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

The formula IF(B2:B10>=0,ROW(B2:B10)) 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 function FREQUENCYLet's calculate the array of consecutive negative numbers. The formula will look like 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 largest value in the array of consecutive negative numbers. In this case, the result will be 3, since there have been three consecutive weeks of losses in the stock market.

VBA Solution: Custom Function to Count Negative Numbers

In addition to the formula shown above, 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  m Then
        m = c
      End If
    Else
      c = 0
    End If
  Next r
  
  ContaNumeriNegativi = m
  
End Function

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

Counting Consecutive Positive Numbers

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

=MAX(FREQUENCY(IF(B2:B10>0,ROW(B2:B10)),IF(B2:B10
Adding Consecutive Negative Values
In the previous example, we calculated the number of consecutive negative numbers. However, if you need to add consecutive negative values, you can use the following formula:
=MIN((COUNTIF(OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,C2),"
Please note that the example provided is for demonstration purposes only, and adding percentages is meaningless. Counting consecutive positive or negative numbers in an Excel spreadsheet may seem like a complicated task, but using the right 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 helpful and makes your work with Excel easier.
  Published in Excel

March 6, 2024
Paolo Bergamaschi
Excel
0