**Please Note: **
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 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.

Written by Allen Wyatt (last updated October 2, 2021)**This tip applies to** Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

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 (11105) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel 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!

Cells in a worksheet can contain different types of information, such as numbers, text, and formulas. If you want to ...

Discover MoreInsert or delete a column, and Excel automatically updates references within formulas that are affected by the change. If ...

Discover MoreIf you have a large amount of data in a worksheet and you want to extract information from the text that meets certain ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2021-10-07 06:11:01

Lars

Mike:

I have office 365.

So that does not explain what is wrong.

2021-10-06 06:45:18

Mike

I am not getting Roy's formula to work either.

If the formula is entered as shown, then I get either 1 or 0, but If I enter the formula as an array formula (ctrl+shft+enter) I always get 1.

Even more bizarre is that I have the exact same formula in F7 and F8, and often get different results, but if I put the formula in F7 and G7, the result is always the same.

The only thing I can think of is that the formula needs Office365 - I am using Excel 2010.

2021-10-05 10:52:28

Lars

Roy:

It only returns 1, no matter how many there are in the column.

2021-10-02 06:28:52

Roy

=MAX( IF( A1:A50>=0, 0, B1:B50+1 ) )

Even though it feels like it should produce an error, it will not. And it matches the helper column data row for row if you strip off the MAX() to check their matching.

Of course, MAX() then returns the greatest value which is what is asked for: the longest run of negative numbers in the data set.

(Technically? "Or tied for" the longest run of negative numbers in the set, but only that value is asked for, not how many sets like that are present, or the address/es for the one or more than one such run.)

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.

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