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

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

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

**Create Custom Apps with VBA!** Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out *Mastering VBA for Office 2013* today!

You can easily sum a series of values in Excel, but it is not so easy to sum the absolute values of each value in a ...

Discover MoreWhen editing information in a cell, you may need to know the result of a portion of your formula. The shortcut described ...

Discover MoreWhen working with data taken from the real world, you often have to determine which certain conditions were met, such as ...

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

## Comments