Page **1** of **1**

### [Solved] How to find alternating numbers in a column

Posted:

**Wed Feb 20, 2019 7:01 pm**
by **withnail**

Hi,

I have a column with thousands of positive and negative numbers like in this example:

110.9

-100

-100

129.9

155.8

-100

120

-100

110

116

-100

117

-100

113.1

-100

I want to be able count how often and for how long the alternating pattern of positive and negative numbers occurs like so

+ - + - + - + - etc,, so

----- and

+++++ can be ignored.

Any idea's?

### Re: How to find alternating numbers in a column

Posted:

**Wed Feb 20, 2019 7:43 pm**
by **John_Ha**

Add a column alongside. In it, if the number in the first column alternates with the one above, set the cell to 1. If not set it to 0. Use another column to count continuous sequences.

You get the idea ...

### Re: How to find alternating numbers in a column

Posted:

**Wed Feb 20, 2019 7:50 pm**
by **withnail**

I get the idea of just scrolling down and searching for the alternating numbers in the helper column, that would be do-able.

But do you mean just set the positive numbers to 0 and the negative's to 1? Or is their a formula which specifically finds an alternation?

### Re: How to find alternating numbers in a column

Posted:

**Wed Feb 20, 2019 7:58 pm**
by **John_Ha**

- Code: Select all Expand viewCollapse view
`Numbers Helper_1 helper_2`

110.9 - -

-100 1

-100 0

-123.4 1

155.8 1

-100 0

110.9 0

-100 1

-100 0

129.9 1

155.8 0

Use helper column 2 to process helper column 1, and so on, or something similar.

### Re: How to find alternating numbers in a column

Posted:

**Wed Feb 20, 2019 8:15 pm**
by **withnail**

Thanks!

I understand what you mean, the second column hadn't occured to me but would help greatly.

Only thing I still don't know how to do is generate the results for the first helper....I mean, your example shows a 1 after the first -100 and then a 0 after the second -100. How do you get it to do that?!

### Re: How to find alternating numbers in a column

Posted:

**Wed Feb 20, 2019 8:31 pm**
by **John_Ha**

Something like this - dinner presses so no more time. You need to think of how to handle the first and last numbers as well.

** Edit: **Edited to correct image and file |

### Re: How to find alternating numbers in a column

Posted:

**Wed Feb 20, 2019 9:43 pm**
by **withnail**

Great John, thanks.

Enjoy your dinner!