**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.

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:*

If you would like to know how to use the macros described on this page (or on any other page on the *ExcelTips* sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

*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**.

**Excel Smarts for Beginners!** Featuring the friendly and trusted *For Dummies* style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out *Excel 2013 For Dummies* today!

A common task faced by Excel users is to determine whether items in one list are also found in a different list. There ...

Discover MoreIn mathematics, the sum of a range of sequential integers, starting with 1, is known as a triangular number or Gaussian ...

Discover MoreFor some operations and functions, Excel allows you to use wild card characters. One such character is an asterisk. What ...

Discover More**FREE 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

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

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 © 2022 Sharon Parq Associates, Inc.

## Comments