countif

[fzl2007]

I want to count the rows if the value of the column, from bottom up,
changes sign (from positive to negative or vise versa.). As soon as
the sign is changed or the number is the value of a zero, counter
finishes counter.

For example,

-2.1 31.2 -2.1
0.3 44.8 1.6
-1.1 -9.4 5.3
44 10.4 9
13.1 -2.2 0
12.2 -11.1 3.2
counter = 3 counter = 2 counter = 1

Thanks.

 

[joeu2004]

I want to count the rows if the value of the column,
from bottom up, changes sign [....]
For example,
-2.1 31.2 -2.1
0.3 44.8 1.6
-1.1 -9.4 5.3
44 10.4 9
13.1 -2.2 0
12.2 -11.1 3.2
counter=3 counter=2 counter=1

For the data in A1:A6, enter the following __array_formula__ (press
ctrl+shift+Enter instead of Enter):

=6-MATCH(2,1/(SIGN(A6)<>SIGN(A1:A5)))

6 can be replaced by COUNT(A1:A6).

MATCH returns the last row number relative to A1 whose sign differs from A6.
6-MATCH(...) makes that row number relative to A6.

Caveat: This returns a #N/A if there is no sign change. If you do not like
that, you can do the following in XL2007 and later:

=IFERROR(6-MATCH(2,1/(SIGN(A6)<>SIGN(A1:A5))),"")

In XL2003 and earlier:

=IF(SUMPRODUCT(--(SIGN(A6)<>SIGN(A1:A5)))>0,
6-MATCH(2,1/(SIGN(A6)<>SIGN(A1:A5))),"")

Both alternatives are also __array_formulas__ (press ctrl+shift+Enter
instead of Enter).

 

[fzl2007]

I want to count the rows if the value of the column,
from bottom up, changes sign [....]
For example,
-2.1         31.2         -2.1
0.3          44.8         1.6
-1.1         -9.4         5.3
44           10.4         9
13.1         -2.2         0
12.2         -11.1        3.2
counter=3    counter=2    counter=1

For the data in A1:A6, enter the following __array_formula__ (press
ctrl+shift+Enter instead of Enter):

=6-MATCH(2,1/(SIGN(A6)<>SIGN(A1:A5)))

6 can be replaced by COUNT(A1:A6).

MATCH returns the last row number relative to A1 whose sign differs from A6.
6-MATCH(...) makes that row number relative to A6.

Caveat:  This returns a #N/A if there is no sign change.  If you do not like
that, you can do the following in XL2007 and later:

=IFERROR(6-MATCH(2,1/(SIGN(A6)<>SIGN(A1:A5))),"")

In XL2003 and earlier:

=IF(SUMPRODUCT(--(SIGN(A6)<>SIGN(A1:A5)))>0,
 6-MATCH(2,1/(SIGN(A6)<>SIGN(A1:A5))),"")

Both alternatives are also __array_formulas__ (press ctrl+shift+Enter
instead of Enter).

I just ran the array formular on this list, =6-MATCH(2,1/
(SIGN(A6)<>SIGN(A1:A5))), the solution should be 2 but it gave a 1...
Also, how do I change the function so that it will catch the column
rows as it changes? The column data (range) is dynamic. Thanks.

6.4
11.2
2.5
7.3
6.1
-5.5
0.2
5.8

 

[joeu2004]

For the data in A1:A6, enter the following __array_formula__
(press ctrl+shift+Enter instead of Enter):
=6-MATCH(2,1/(SIGN(A6)<>SIGN(A1:A5)))
6 can be replaced by COUNT(A1:A6).

[....]
I just ran the array formular on this list, =6-MATCH(2,1/
(SIGN(A6)<>SIGN(A1:A5))), the solution should be 2 but it
gave a 1
6.4
11.2
2.5
7.3
6.1
-5.5
0.2
5.8

There operative words were "for the data in A1:A6". Since your new example
is 8 cells, presumably in A1:A8, the formula needs to be adapted
accordingly, to wit:

=8-MATCH(2,1/(SIGN(A8)<>SIGN(A1:A7)))

Also, how do I change the function so that it will
catch the column rows as it changes? The column data
(range) is dynamic.

The simplest way to do that is to ensure that there is always a cell above
and below (empty or containing text), and you use Insert and Delete to add
and remove data. Then the dynamic formula can be:

=COUNT($A$1:$A$10)
-MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<>SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10)))))

That assumes that A1 and A10 are the rows above and below 8 cells of data.

Caveat: OFFSET is a volatile function. So that formula will be
recalculated every time any cell in any worksheet in the workbook is edited.
If you have a lot of such formulas, that can degrade performance
significantly.

 

[fzl2007]

For the data in A1:A6, enter the following __array_formula__
(press ctrl+shift+Enter instead of Enter):
=6-MATCH(2,1/(SIGN(A6)<>SIGN(A1:A5)))
6 can be replaced by COUNT(A1:A6).

[....]
I just ran the array formular on this list,  =6-MATCH(2,1/
(SIGN(A6)<>SIGN(A1:A5))), the solution should be 2 but it
gave a 1
6.4
11.2
2.5
7.3
6.1
-5.5
0.2
5.8

There operative words were "for the data in A1:A6".  Since your new example
is 8 cells, presumably in A1:A8, the formula needs to be adapted
accordingly, to wit:

=8-MATCH(2,1/(SIGN(A8)<>SIGN(A1:A7)))

Also, how do I change the function so that it will
catch the column rows as it changes? The column data
(range) is dynamic.

The simplest way to do that is to ensure that there is always a cell above
and below (empty or containing text), and you use Insert and Delete to add
and remove data.  Then the dynamic formula can be:

=COUNT($A$1:$A$10)
-MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<>SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10­)))))

That assumes that A1 and A10 are the rows above and below 8 cells of data..

Caveat:  OFFSET is a volatile function.  So that formula will be
recalculated every time any cell in any worksheet in the workbook is edited.
If you have a lot of such formulas, that can degrade performance
significantly.- Hide quoted text -

- Show quoted text -

The column data will be added and the fomula needs to capture the new
data added. There are many such columns. This will not be a good
solution as we will need to update the formula every time when data
changes.

I wonder if there is another solution... that will capture the range
with data for the column...

 

[Don Guillett]

For the data in A1:A6, enter the following __array_formula__
(press ctrl+shift+Enter instead of Enter):
=6-MATCH(2,1/(SIGN(A6)<>SIGN(A1:A5)))
6 can be replaced by COUNT(A1:A6). [....]
I just ran the array formular on this list,  =6-MATCH(2,1/
(SIGN(A6)<>SIGN(A1:A5))), the solution should be 2 but it
gave a 1
6.4
11.2
2.5
7.3
6.1
-5.5
0.2
5.8

There operative words were "for the data in A1:A6".  Since your new example
is 8 cells, presumably in A1:A8, the formula needs to be adapted
accordingly, to wit:

The simplest way to do that is to ensure that there is always a cell above
and below (empty or containing text), and you use Insert and Delete to add
and remove data.  Then the dynamic formula can be:

=COUNT($A$1:$A$10)
-MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<>SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10 ­)))))

That assumes that A1 and A10 are the rows above and below 8 cells of data.

Caveat:  OFFSET is a volatile function.  So that formula will be
recalculated every time any cell in any worksheet in the workbook is edited.
If you have a lot of such formulas, that can degrade performance
significantly.- Hide quoted text -

- Show quoted text -

The column data will be added and the fomula needs to capture the new
data added. There are many such columns. This will not be a good
solution as we will need to update the formula every time when data
changes.

I wonder if there is another solution... that will capture the range
with data for the column...

Will a macro do?

Sub MarkChangeSAS()
Dim r As Long
Dim c As Long

Rows(10).ClearContents
For c = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
For r = Cells(Rows.Count, c).End(xlUp).Row To 2 Step -1
If Sgn(Cells(r, c)) <> Sgn(Cells(r - 1, c)) Or Cells(r, c) = 0 Then
Cells(10, c) = Cells(Rows.Count, c).End(xlUp).Row - r + 1
Exit For
End If
Next r
Next c
End Sub

 

[joeu2004]

=COUNT($A$1:$A$10)
-MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<>SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10­)))))

[....]
The column data will be added and the fomula needs to
capture the new data added. There are many such columns.
This will not be a good solution as we will need to
update the formula every time when data changes.

That depends on how you are capturing the data. Again, if you can
__insert__ data between A1 and A10, the formula will adjust automagically.

More generally, assuming that column B contains only your data starting in
row 2 (specially, no numeric data after; text before and after is okay), you
could use the following __array_formula__ (press ctrl+shift+Enter instead of
just Enter):

=COUNT(B2:INDEX(B:B,MATCH(1E+300,B:B)))
- MATCH(2,1/(SIGN(INDEX(B:B,MATCH(1E+300,B:B)))
<>SIGN(B2:INDEX(B:B,MATCH(1E+300,B:B)))))

Note: Given the assumptions, you could write COUNT(B:B) instead. I wanted
to show the more general form just in case some of the assumptions do not
apply.

That is an extremely inefficient formula, particularly in XL2007 and later
with a limit 1M rows (1M = 1,048,576).

If you know your data will not extend to more than row 10,000, it would be
much better to replace B:B with B1:B10000. (Note: B1, not B2.)

It would be even better if you put =MATCH(1E300,B1:B10000) into a helper
cell (X1), and replace the repeated MATCH expressions with X1 in the
formula.

And if all of the "many such columns" are the same length, it would be
better if you also put =COUNT(B2:INDEX(B:B,X1)) into a helper cell (X2).

Thus, you formula becomes:

=X2-MATCH(2,1/(SIGN(INDEX(B:B,X1))<>SIGN(B2:INDEX(B:B,X1))))

The good news is: INDEX is not a volatile function. The formula is
recalculated only when column B changes.

However, it appears that Excel does recalculate formulas with INDEX when the
file is opened. So you might experience some delay at the outset; and you
will be prompted to save or not when you close the file, even if you did not
make any changes yourself.

 

[fzl2007]

=COUNT($A$1:$A$10)
-MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<>SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10­­)))))

[....]
The column data will be added and the fomula needs to
capture the new data added. There are many such columns.
This will not be a good solution as we will need to
update the formula every time when data changes.

That depends on how you are capturing the data.  Again, if you can
__insert__ data between A1 and A10, the formula will adjust automagically..

More generally, assuming that column B contains only your data starting in
row 2 (specially, no numeric data after; text before and after is okay), you
could use the following __array_formula__ (press ctrl+shift+Enter insteadof
just Enter):

=COUNT(B2:INDEX(B:B,MATCH(1E+300,B:B)))
- MATCH(2,1/(SIGN(INDEX(B:B,MATCH(1E+300,B:B)))
<>SIGN(B2:INDEX(B:B,MATCH(1E+300,B:B)))))

Note:  Given the assumptions, you could write COUNT(B:B) instead.  I wanted
to show the more general form just in case some of the assumptions do not
apply.

That is an extremely inefficient formula, particularly in XL2007 and later
with a limit 1M rows (1M = 1,048,576).

If you know your data will not extend to more than row 10,000, it would be
much better to replace B:B with B1:B10000.  (Note:  B1, not B2.)

It would be even better if you put =MATCH(1E300,B1:B10000) into a helper
cell (X1), and replace the repeated MATCH expressions with X1 in the
formula.

And if all of the "many such columns" are the same length, it would be
better if you also put =COUNT(B2:INDEX(B:B,X1)) into a helper cell (X2)..

Thus, you formula becomes:

=X2-MATCH(2,1/(SIGN(INDEX(B:B,X1))<>SIGN(B2:INDEX(B:B,X1))))

The good news is:  INDEX is not a volatile function.  The formula is
recalculated only when column B changes.

However, it appears that Excel does recalculate formulas with INDEX when the
file is opened.  So you might experience some delay at the outset; and you
will be prompted to save or not when you close the file, even if you did not
make any changes yourself.

Hi Joe,
This worked like a charm,

=COUNT(B2:INDEX(B:B,MATCH(1E+300,B:B)))
- MATCH(2,1/(SIGN(INDEX(B:B,MATCH(1E+300,B:B)))
<>SIGN(B2:INDEX(B:B,MATCH(1E+300,B:B)))))

Now. I have data on "Sheet1" and want results on "Sheet2". How do I
apply your formula so that it will look up every other column starting
Column B on "Sheet1" and place the result on F6
"Sheet2", Column D on "Sheet1" and place result on F7 "Sheet2";
Column
F on "Sheet1" and place result on F8 on "Sheet2" and on ... until
column is empty?

Thanks again.
Faye

 

[joeu2004]

=COUNT(B2:INDEX(B:B,MATCH(1E+300,B:B)))
- MATCH(2,1/(SIGN(INDEX(B:B,MATCH(1E+300,B:B)))
<>SIGN(B2:INDEX(B:B,MATCH(1E+300,B:B)))))

Now. I have data on "Sheet1" and want results
on "Sheet2". How do I apply your formula so
that it will look up every other column starting
Column B on "Sheet1" and place the result on F6
"Sheet2", Column D on "Sheet1" and place result
on F7 "Sheet2"; Column F on "Sheet1" and place
result on F8 on "Sheet2" and on ... until
column is empty?

I hope someone else can step up and help you with that.

I can tell you that it is possible to do, and I could develop the formula.

But there are trade-offs to consider and some testing. I just don't have
that kind of time at the moment.

I will tell you that a solution based on repeated instances of my formula
above will become very time-consuming without boundaries.

So you might consider posting some reasonable boundaries. For example,
first and last possible row that might contain data, as well as last
possible column in Sheet1.

And you might consider whether a VBA macro solution would be acceptable.
Even if the VBA implementation might be slower, at least it would only run
when you decide to execute it. It could be implemented as "button" that you
can click after downloading new data.

One final caveat about my formula.... I just realized that I assume that
the numbers start in row 2 (of all columns). And they are contiguous
through the last row; no gaps in the data.

Are those assumptions acceptable?

Good luck with this.

 

[fzl2007]

I hope someone else can step up and help you with that.

I can tell you that it is possible to do, and I could develop the formula..

But there are trade-offs to consider and some testing.  I just don't have
that kind of time at the moment.

I will tell you that a solution based on repeated instances of my formula
above will become very time-consuming without boundaries.

So you might consider posting some reasonable boundaries.  For example,
first and last possible row that might contain data, as well as last
possible column in Sheet1.

And you might consider whether a VBA macro solution would be acceptable.
Even if the VBA implementation might be slower, at least it would only run
when you decide to execute it.  It could be implemented as "button" that you
can click after downloading new data.

One final caveat about my formula....  I just realized that I assume that
the numbers start in row 2 (of all columns).  And they are contiguous
through the last row; no gaps in the data.

Are those assumptions acceptable?

Good luck with this.

Yes, your assumptions are correct with one thing only, the column
might be of the same sign and the result of the count for the rows
would be desired. Now it would show #NA...

Thank you for your time. I greatly appreciate it.