Written by Allen Wyatt (last updated April 13, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
Lori has a series of numbers, in adjacent cells, that can be either positive or negative. She would like a way to determine the largest sequence of negative numbers in the range. Thus, if there were seven negative numbers in a row in this sequence, she would like a formula that would return the value 7.
We've looked high and low and can't find a single formula that will do what is wanted. You can, however, do it with an intermediate column. For instance, if you have your numbers in column A (beginning in A1), then you could put the following formula in cell B1:
=IF(A1<0,1,0)
Then, in cell B2 enter the following:
=IF(A2<0,B1+1,0)
Copy this down to all the other cells in column B for which there is a value in column A. Then, in a different cell (perhaps cell C1) you can put the following formula:
=MAX(B:B)
This value will represent the largest number of consecutive negative values in column A.
If you don't want to create an intermediate column to get the answer, you could create a user-defined function that will return the value.
Function MaxNegSequence(rng As Range) ' search for the largest sequence ' of negative numbers in the range Dim c As Range Dim lCounter As Long Dim lMaxCount As Long Application.Volatile lCounter = 0 lMaxCount = 0 On Error Resume Next For Each c In rng.Cells If c.Value < 0 Then lCounter = lCounter + 1 If lCounter > lMaxCount Then lMaxCount = lCounter End If Else lCounter = 0 End If Next c MaxNegSequence = lMaxCount End Function
To use the function, just place a formula similar to the following in your worksheet:
= MaxNegSequence(A1:A512)
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3533) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Counting Consecutive Negative Numbers.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Discovering different ways to analyze your data can be a challenge. Here's how to work with arbitrary subsets of a large ...
Discover MoreIf a series of cells contain the amount of money won by individuals, you may want to count the number of individuals who ...
Discover MoreIf you use serial numbers that include both letters and numbers, you might wonder how you can increment the numeric ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-10-15 20:03:48
Wilton
Hello Allen,
Thank you for the Counting Consecutive Negative Numbers code. I have a question regarding this line of code: Dim lMaxCount As Long
When stepping through the code, the lMaxCount variable becomes a Boolean data type. Since this variable is clearly dim as a long data type, why does it become a Boolean data type when the code starts running?
Thank you for any insights .
Wilt
2019-04-13 14:36:28
Rick Rothstein
Here is another, more compact way to write your MaxNegSequence function...
Function MaxNegSequence(Rng As Range) As Long
Dim V As Variant, Arr As Variant
Application.Volatile
For Each V In Split(Replace(Join(Evaluate("TRANSPOSE(IF(" & Rng.Address & "<0,1,""""))"), " "), "1 ", 1))
If Len(V) > MaxNegSequence Then MaxNegSequence = Len(V)
Next
End Function
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments