Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Counting consecutive negative value

Hi,

I have a column with positive and negative numbers.
I need to count the largest number of consecutive negative numbers and don't
really know how to do it.
Thanks in advance.

Wallace
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 905
Default Counting consecutive negative value

"Wallace" wrote:
I have a column with positive and negative numbers.
I need to count the largest number of consecutive
negative numbers and don't really know how to do it.


First, do you really want to count "consecutive negative numbers" per se, or
do you really want to count "number of consecutive cells with negative
numbers"?

In the first case, consecutive cells with values -1, -2 and -4 would count
as 2; in the second case, they would count as 3.

You should be able to adapt the following approach in either case. But if
you need assistance with that, you need to be more precise.

This might not be the best approach, but it's simple. I believe someone (T
"Biff" Valko?) was able to implement this approach in a single array formula.

Suppose your numbers are in A1:A30. In B1, put the formula =--(A1<0). And
put the following formula into B2 and copy down: =(A2<0)*(B1+1).

Then =MAX(B1:B30) is the largest number of consecutive cells with negative
numbers.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default Counting consecutive negative value

Wallace wrote...
I have a column with positive and negative numbers.
I need to count the largest number of consecutive negative numbers and don't
really know how to do it.


If you mean consecutive cells containing negative numbers, you could
do this in a single cell array formula. If the data were in a single
column, multiple row range named X, try the array formula

=MAX(LARGE((ROW(X)-MIN(ROW(X))+1)*(X<0)
*SIGN((MMULT(--(ROW(X)+1=TRANSPOSE(ROW(X))),--X)=0)
+(ROW(X)=MAX(ROW(X)))),ROW(X)-MIN(ROW(X))+1)
-LARGE((ROW(X)-MIN(ROW(X)))*(X<0)
*SIGN((MMULT(--(ROW(X)-1=TRANSPOSE(ROW(X))),--X)=0)
+(ROW(X)=MIN(ROW(X)))),ROW(X)-MIN(ROW(X))+1))

Simpler using ancillary formulas/cells.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Counting consecutive negative value

Try this array formula** :

=MAX(FREQUENCY(IF(A2:A25<0,ROW(A2:A25)),IF(A2:A25 =0,ROW(A2:A25))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Wallace" wrote in message
...
Hi,

I have a column with positive and negative numbers.
I need to count the largest number of consecutive negative numbers and
don't
really know how to do it.
Thanks in advance.

Wallace



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Counting consecutive negative value

Explanation on how that formula works:

http://www.mrexcel.com/forum/showpos...78&postcount=9

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this array formula** :

=MAX(FREQUENCY(IF(A2:A25<0,ROW(A2:A25)),IF(A2:A25 =0,ROW(A2:A25))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Wallace" wrote in message
...
Hi,

I have a column with positive and negative numbers.
I need to count the largest number of consecutive negative numbers and
don't
really know how to do it.
Thanks in advance.

Wallace







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default Counting consecutive negative value

Slight variation, which doesn't need to be array entered:

=LARGE(FREQUENCY((A2:A25<0)*ROW(A2:A25),(A2:A25=0 )*ROW(A2:A25)),2)

MAX in this case would return the total number of negative values, so
LARGE(,2) is the figure we're looking for.



"T. Valko" wrote in message
...
Try this array formula** :

=MAX(FREQUENCY(IF(A2:A25<0,ROW(A2:A25)),IF(A2:A25 =0,ROW(A2:A25))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Wallace" wrote in message
...
Hi,

I have a column with positive and negative numbers.
I need to count the largest number of consecutive negative numbers and
don't
really know how to do it.
Thanks in advance.

Wallace




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 905
Default Counting consecutive negative value

"Steve Dunn" wrote:
Slight variation, which doesn't need to be array entered:
=LARGE(FREQUENCY((A2:A25<0)*ROW(A2:A25),(A2:A25=0 )*ROW(A2:A25)),2)


Does not work in a number of specific cases. For example, when A2:A25
contains only negative numbers. For another example, 12 consecutive
negative numbers, then zero, then 11 consecutive negative numbers.

Interestingly, using MAX instead of LARGE works in those cases. But MAX
does not work in random cases. I suspect for the same reason that LARGE
fails in the cases above.

Apparently, the problem is: as written above, the first FREQUENCY parameter
contains zeroes for cells where the condition is false. Those increase the
"bin" for the first row that meets the condition A2:A25=0.

In contrast, with the array formula using IF(A2:A25<0,ROW(A2:A25)), the
first FREQUENCY parameter contains FALSE truth values instead of zero.
Apparently, FREQUENCY ignores truth values as well as blank and text cells.
(Not documented in the Excel 2003 offline Help page.)


----- original message -----

"Steve Dunn" wrote in message
...
Slight variation, which doesn't need to be array entered:

=LARGE(FREQUENCY((A2:A25<0)*ROW(A2:A25),(A2:A25=0 )*ROW(A2:A25)),2)

MAX in this case would return the total number of negative values, so
LARGE(,2) is the figure we're looking for.



"T. Valko" wrote in message
...
Try this array formula** :

=MAX(FREQUENCY(IF(A2:A25<0,ROW(A2:A25)),IF(A2:A25 =0,ROW(A2:A25))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Wallace" wrote in message
...
Hi,

I have a column with positive and negative numbers.
I need to count the largest number of consecutive negative numbers and
don't
really know how to do it.
Thanks in advance.

Wallace





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
counting of consecutive days over a certain value joe Excel Worksheet Functions 0 August 20th 08 03:48 AM
Counting Consecutive Instances carl Excel Worksheet Functions 3 September 24th 07 07:08 PM
Counting Consecutive Cells SteveC Excel Discussion (Misc queries) 16 June 21st 06 06:39 PM
Counting Consecutive Improvements SteveC Excel Discussion (Misc queries) 0 June 13th 06 01:35 AM
count the # of consecutive negative #'s in a range newToExcel Excel Discussion (Misc queries) 5 November 13th 05 02:14 AM


All times are GMT +1. The time now is 12:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"