count current run of positive numbers
 

hi, in Column M i have a list of results that will go on down the page as
values are entered elsewhere, these results will be both negative and
positive, i was after a formula that will give me the count of the current
run of positive numbers, eg. below, the cell with the formula in it would
return a value of 4 for the current run of positive numbers, and if there was
a run of negative numbers it would be 0

2
5
6
-5
2
4
7
9
--
thanks

count current run of positive numbers
 

Will there be any numeric 0 entries?

a list of results that will go on down the
page as values are entered elsewhere

That sounds like you'll need to use a dynamic range.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi, in Column M i have a list of results that will go on down the page as
values are entered elsewhere, these results will be both negative and
positive, i was after a formula that will give me the count of the current
run of positive numbers, eg. below, the cell with the formula in it would
return a value of 4 for the current run of positive numbers, and if there
was
a run of negative numbers it would be 0

2
5
6
-5
2
4
7
9
--
thanks

count current run of positive numbers
 

I dont know what you want to get, whether the Sum of positive & Negative Nos
or Count€¦ Anyway try the below.

This formula will get you the Count of Positive Nos.
=COUNTIF(M:M,"0")

This formula will get you the Count of Negative Nos.
=COUNTIF(M:M,"<0")

This formula will get you the Sum of Positive Nos.
=SUMIF(M:M,"0",M:M)

This formula will get you the Sum of Negative Nos.
=SUMIF(M:M,"<0",M:M)

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Morgan" wrote:

hi, in Column M i have a list of results that will go on down the page as
values are entered elsewhere, these results will be both negative and
positive, i was after a formula that will give me the count of the current
run of positive numbers, eg. below, the cell with the formula in it would
return a value of 4 for the current run of positive numbers, and if there was
a run of negative numbers it would be 0

2
5
6
-5
2
4
7
9
--
thanks

count current run of positive numbers
 

hi there, no there won't be any 0 entries, each day a value will returned in
column M that will be either positive or negative, i was just after a formula
that would return the current streak of days that have been positive, if such
a formula exists, thanks for your help
--
thanks


"T. Valko" wrote:

Will there be any numeric 0 entries?

a list of results that will go on down the
page as values are entered elsewhere

That sounds like you'll need to use a dynamic range.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi, in Column M i have a list of results that will go on down the page as
values are entered elsewhere, these results will be both negative and
positive, i was after a formula that will give me the count of the current
run of positive numbers, eg. below, the cell with the formula in it would
return a value of 4 for the current run of positive numbers, and if there
was
a run of negative numbers it would be 0

2
5
6
-5
2
4
7
9
--
thanks


.

count current run of positive numbers
 

there won't be any 0 entries
if there was a run of negative numbers it would be 0

Ok, try this array formula** :

=LOOKUP(1E100,FREQUENCY(IF(M2:M200,ROW(M2:M20)),I F(M2:M20<0,ROW(M2:M20))))

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

Adjust for a reasonable end of range. May be better to use a dynamic range.
The formula will ignore empty cells. For example:

4
3
-1
2
<empty
5
1

The result would be 3 counting the 1, 5 and 2.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi there, no there won't be any 0 entries, each day a value will returned
in
column M that will be either positive or negative, i was just after a
formula
that would return the current streak of days that have been positive, if
such
a formula exists, thanks for your help
--
thanks


"T. Valko" wrote:

Will there be any numeric 0 entries?

a list of results that will go on down the
page as values are entered elsewhere

That sounds like you'll need to use a dynamic range.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi, in Column M i have a list of results that will go on down the page
as
values are entered elsewhere, these results will be both negative and
positive, i was after a formula that will give me the count of the
current
run of positive numbers, eg. below, the cell with the formula in it
would
return a value of 4 for the current run of positive numbers, and if
there
was
a run of negative numbers it would be 0

2
5
6
-5
2
4
7
9
--
thanks


.

count current run of positive numbers
 

it works perfectly, thank you so much!
--
thanks


"T. Valko" wrote:

there won't be any 0 entries
if there was a run of negative numbers it would be 0

Ok, try this array formula** :

=LOOKUP(1E100,FREQUENCY(IF(M2:M200,ROW(M2:M20)),I F(M2:M20<0,ROW(M2:M20))))

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

Adjust for a reasonable end of range. May be better to use a dynamic range.
The formula will ignore empty cells. For example:

4
3
-1
2
<empty
5
1

The result would be 3 counting the 1, 5 and 2.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi there, no there won't be any 0 entries, each day a value will returned
in
column M that will be either positive or negative, i was just after a
formula
that would return the current streak of days that have been positive, if
such
a formula exists, thanks for your help
--
thanks


"T. Valko" wrote:

Will there be any numeric 0 entries?

a list of results that will go on down the
page as values are entered elsewhere

That sounds like you'll need to use a dynamic range.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi, in Column M i have a list of results that will go on down the page
as
values are entered elsewhere, these results will be both negative and
positive, i was after a formula that will give me the count of the
current
run of positive numbers, eg. below, the cell with the formula in it
would
return a value of 4 for the current run of positive numbers, and if
there
was
a run of negative numbers it would be 0

2
5
6
-5
2
4
7
9
--
thanks


.



.

count current run of positive numbers
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
it works perfectly, thank you so much!
--
thanks


"T. Valko" wrote:

there won't be any 0 entries
if there was a run of negative numbers it would be 0

Ok, try this array formula** :

=LOOKUP(1E100,FREQUENCY(IF(M2:M200,ROW(M2:M20)),I F(M2:M20<0,ROW(M2:M20))))

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

Adjust for a reasonable end of range. May be better to use a dynamic
range.
The formula will ignore empty cells. For example:

4
3
-1
2
<empty
5
1

The result would be 3 counting the 1, 5 and 2.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi there, no there won't be any 0 entries, each day a value will
returned
in
column M that will be either positive or negative, i was just after a
formula
that would return the current streak of days that have been positive,
if
such
a formula exists, thanks for your help
--
thanks


"T. Valko" wrote:

Will there be any numeric 0 entries?

a list of results that will go on down the
page as values are entered elsewhere

That sounds like you'll need to use a dynamic range.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi, in Column M i have a list of results that will go on down the
page
as
values are entered elsewhere, these results will be both negative
and
positive, i was after a formula that will give me the count of the
current
run of positive numbers, eg. below, the cell with the formula in it
would
return a value of 4 for the current run of positive numbers, and if
there
was
a run of negative numbers it would be 0

2
5
6
-5
2
4
7
9
--
thanks


.



.