Count instances of positive or negative numbers in a row

[Eugenius]

Hello this is my first post here. Hopefully I can be clear with my query so you all you excel genii might help.

Here is a sequence of numbers in a column

100
20
-78
5
-99
-54
-34
6
0
34
55
89
32
-3
-78

Now I need to COUNT the number of instances of the following data set. The answer is in brackets:

1 positive number on its own (2)
2 positive values in a row (1)
3 positive values in a row (0)
4 positive values in a row (1)

1 0 value on its own (1)
2 0 values in a row (0)
3 0 values in a row (0)
4 0 values in a row (0)

1 negative number on its own (1)
2 negative values in a row (1)
3 negative values in a row (1)
4 negative values in a row (0)

Any ideas on excel formula very welcome!

 

[Oaktree]

Welcome to the board!

I'd do this in steps:

1) With your data in A1:A15, set B1 =SIGN(A1) and copy that down through B15. That column will have a 1 for positive values, a 0 for zeroes, and a -1 for negative values.

2) Enter the value 1 in cell C1. Set C2 =IF(B2<>B1,1,C1+1) and copy down through C15. This will tell you how many consecutive similarly signed values you have up to the current row. For example, a 2 means that this cell and the cell above it have the same sign. A 1 means the cell above it has a different sign.

3) Set D1 =IF(C2=1,C1,IF(C2="",C1,"")) and copy down through D15. This takes the values in column C and makes them blank if they're not the last value in the current sequence.

4) Use =SUMPRODUCT(--($B$1:$B$15=1),--($D$1:$D$15=4)) to count the number of 4 positive values in a row. You'd copy the formula and change the "4" to be the 1,2,3,4 values in a row, and you'd change the "=1" to be =1 for positive values, =0 for zeroes, and =-1 for negatives.

 

[Eugenius]

Wow thanks Oaktree that certainly does it...no wonder I couldn't figure it out!

Now I've got work out whether it's a practical approach to formatting thousands of lines of data. Hopefully it will be.

All the best

Eugenius

Welcome to the board!

I'd do this in steps:

1) With your data in A1:A15, set B1 =SIGN(A1) and copy that down through B15. That column will have a 1 for positive values, a 0 for zeroes, and a -1 for negative values.

2) Enter the value 1 in cell C1. Set C2 =IF(B2<>B1,1,C1+1) and copy down through C15. This will tell you how many consecutive similarly signed values you have up to the current row. For example, a 2 means that this cell and the cell above it have the same sign. A 1 means the cell above it has a different sign.

3) Set D1 =IF(C2=1,C1,IF(C2="",C1,"")) and copy down through D15. This takes the values in column C and makes them blank if they're not the last value in the current sequence.

4) Use =SUMPRODUCT(--($B$1:$B$15=1),--($D$1:$D$15=4)) to count the number of 4 positive values in a row. You'd copy the formula and change the "4" to be the 1,2,3,4 values in a row, and you'd change the "=1" to be =1 for positive values, =0 for zeroes, and =-1 for negatives.