Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 July 4, 2015)
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, and 2013. You can find a version of this tip for the older menu interface of Excel here: Counting Consecutive Negative Numbers.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
You could use Excel to collect data that is useful in your business. For instance, you might use it to collect ...Discover More
Excel is used by many people to hold all sorts of data, not just numbers. If you have cells that include meaningful ...Discover More
Searching for a value using Excel's Find tool is easy; searching for that same value using a formula or a macro is more ...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.