Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Counting Consecutive Negative Numbers.
by Allen Wyatt
(last updated June 20, 2018)
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:
Then, in cell B2 enter the following:
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:
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:
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Excel makes it easy to concatenate (or combine) different values into a single cell. If you need to combine a different ...Discover More
Need to know a count of unique values in a range of cells? There are a couple of ways you can tackle the problem.Discover More
When working with data taken from the real world, you often have to determine which certain conditions were met, such as ...Discover More
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.