Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Counting Consecutive Negative Numbers.
by Allen Wyatt
(last updated April 29, 2019)
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 (11105) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Counting Consecutive Negative Numbers.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
If your worksheet formulas seem to go on forever, here's a handy way to make them more understandable. (All you need to ...Discover More
Part numbers can often be long, made up of other component elements. Breaking up part numbers into individual components ...Discover More
Text placed in cells can either be lowercase, uppercase, or a mixture of the two. If you want to count the cells based ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.