[Solved] How to find alternating numbers in a column

Discuss the spreadsheet application
Post Reply
withnail
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

[Solved] How to find alternating numbers in a column

Post 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? :-)
Last edited by Hagar Delest on Wed Feb 20, 2019 11:20 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.7
Windows 10
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: How to find alternating numbers in a column

Post 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 ...
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
withnail
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: How to find alternating numbers in a column

Post 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?
OpenOffice 4.1.7
Windows 10
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: How to find alternating numbers in a column

Post by John_Ha »

Code: Select all

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.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
withnail
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: How to find alternating numbers in a column

Post 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?!
OpenOffice 4.1.7
Windows 10
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: How to find alternating numbers in a column

Post 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.
Clipboard01.gif
 Edit: Edited to correct image and file 
Attachments
numbers.ods
(12.62 KiB) Downloaded 98 times
Last edited by John_Ha on Thu Feb 21, 2019 1:01 am, edited 1 time in total.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
withnail
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: How to find alternating numbers in a column

Post by withnail »

Great John, thanks.
Enjoy your dinner! :-)
OpenOffice 4.1.7
Windows 10
Post Reply