counting sign changes

is there any case
-1 -1 0 1 -1 -> 2 signs?

Originally Posted by tigertiger

More exactl and general (for change between negative and positve, or vice versa)
* Asuming A1:E1 that contains the data, F1 is result-cell
* at F1; enter the formular
=SUMPRODUCT(((A1:D1>=0)<>(B1:E1>=0))*1)

This doesn't seem to work in all cases. For example, the following: -1 -1 0 -1 1
returns a value of 3 while it should be one.

Howard

Originally Posted by NBVC

The difficulty is in ignoring the 0's (especially if there is more than one 0 in a row....

I think the easiest way would be to produce a duplicate table that replaces the 0's with the next 1 in sequence so that the 0 doesn't cause a sign change...

so if you have a row of values in A1:E1, then say in G1 enter formula:

=IF(A1=0,IFERROR(INDEX(A1:$E1,MATCH(TRUE,INDEX(A1:$E1<>0,0),0)),F1),A1)

copied across 5 columns and then down as far as you need to convert all entries.

Then you can use formula

=SUMPRODUCT(--(H1:K1<>G1:J1))

on that new table and copy down.

That seems to work if all values are -1, 0 or +1, but does not work more generally. For example: {-2 0 -1 1 -1} returns a value of 3 whereas it should be 2. Is there an easy way to replace any negative/positive value in the original table with -1/+1 in the duplicate, which should solve this?

Thanks!
Howard

Originally Posted by HRundle

That seems to work if all values are -1, 0 or +1, but does not work more generally. For example: {-2 0 -1 1 -1} returns a value of 3 whereas it should be 2. Is there an easy way to replace any negative/positive value in the original table with -1/+1 in the duplicate, which should solve this?

Thanks!
Howard

why you don't try with option in post #6

More exactl and general (for change between negative and positve, or vice versa)
* Asuming A1:E1 that contains the data, F1 is result-cell
* at F1; enter the formular
=SUMPRODUCT(((A1:D1>=0)<>(B1:E1>=0))*1)
.

try at F1 with simple formula:

=SUMPRODUCT(((A1:D1<=0)<>(B1:E1<=0))*1)

Originally Posted by tigertiger

why you don't try with option in post #6

More exactl and general (for change between negative and positve, or vice versa)
* Asuming A1:E1 that contains the data, F1 is result-cell
* at F1; enter the formular
=SUMPRODUCT(((A1:D1>=0)<>(B1:E1>=0))*1)
.

Good idea and I've been trying that. However, there is a circular reference problem in the last cell of the formula to create the duplicate table. When you copy

=IF(A1=0,IFERROR(INDEX(A1:$E1,MATCH(TRUE,INDEX(A1:$E1<>0,0),0)),F1),A1)

across the 5 columns, if the value in the final column is zero you get a circular reference problem.

Cheers,
Howard

you should read carefully

* Asuming A1:E1 that contains the data, F1 is result-cell
* at F1; enter the formular
=SUMPRODUCT(((A1:D1<=0)<>(B1:E1<=0))*1)

you can copy fill for next row F2, F3,....

Originally Posted by NBVC

I assumed you only had 0, 1, -1 .... Try replacing helper column formula with:

=SIGN(IF(A1=0,IFERROR(INDEX(A1:$E1,MATCH(TRUE,INDEX(A1:$E1<>0,0),0)),F1),A1))

copy down and across and use the Sumproduct() formula for final calcs.

That seems to work perfectly.

Many thanks!
Howard

Originally Posted by NBVC

I am not sure if you are mixing my suggestion with other people's suggestions...

See attachment for my suggestion... which works if there is a 0 in last column.

Indeed I was, but figured it out. You final post confirmed it.

Thanks again,
Howard

Originally Posted by daddylonglegs

Does this work, though. If data is, for instance

0,1,1,1,0 shouldn't the result be 0?

YES it is false
It is complex than we though

Hope the one is accurate,

at F1
=SUMPRODUCT((A1:D1<>B1:E1)*(A1:D1<>0)*(B1:E1<>0))

Originally Posted by tigertiger

YES it is false
It is complex than we though

Hope the one is accurate,

at F1
=SUMPRODUCT((A1:D1<>B1:E1)*(A1:D1<>0)*(B1:E1<>0))

Thanks, NBVC's solution seems to work perfectly so my problem is solved.