How to count the consecutive positive / negative numbers in Excel

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

You are struggling with a sheet of calculation inExcelWhich contains both positive and negative numbers? Do you 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, the situation is slightly complicated. In this article, I will show you an effective solution to count the consecutive negative numbers in Excel, illustrating it with a practical example.

Example

Suppose we have a series of data relating to the weekly stock market and we are asked to calculate the number of consecutive weeks in which the market has undergone losses. The data are organized in two columns, with the header in line 1 and data that start from cell A2 and end in cell B10.

To calculate the number of consecutive weeks with losses, enter the following formula in cell C2 and prizesCtrl + Shift + EnterTo confirm the formula as a matricial formula:

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

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

How the Formula works

The formula uses the functionFREQUENCYTo calculate the number of times in which consecutive negative numbers appear. Below, I will explain step by step how the formula returns the desired result.

Step 1: Find the righteous numbers of values ​​below 0

The formulaIF(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 or equal to 0

The formulaIF(B2:B10>=0,ROW(B2:B10))Returns an array with the row number of values ​​that are greater than or equal to 0. For example, if the positive numbers are found in lines 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 functionFREQUENCY, 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 array

The final result of the formula will be the maximum value present in the array of consecutive negative numbers. In this case, the result will be 3, since there have been 3 consecutive weeks of losses on the stock market.

VBA solution: personalized function to count negative numbers

In addition to the formula shown previously, a VBA solution can be used to calculate consecutive negative numbers in Excel. Below you will find a personalized function that you can use to obtain 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 function, enter=ContaNumeriNegativi(B2:B10)in cell C2. The result will be the same obtained using the previous formula.

Count the consecutive positive numbers

Similarly, it is possible to calculate the number of times in which consecutive positive numbers appear. The formula will be almost identical to that 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
Add the consecutive negative values
In the previous example we calculated the number of consecutive negative numbers. However, if you need to add the consecutive negative values, you can use the following formula:
=MIN((COUNTIF(OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,C2),"
Keep in mind that the example provided is only for demonstrative purposes and that adding percentage values ​​does not make sense. Count the consecutive positive or negative numbers in an Excel spreadsheet may seem like a complicated task, but using the correct formulas and functions, it is possible to obtain the desired results. The formula and the VBA solution presented in this article will allow you to count the positive or negative numbers efficiently. I hope this information has been useful to you and that they can facilitate your work with Excel.
 Published inExcel