## [Solved] How to find longest recurring sequence in a column

### [Solved] How to find longest recurring sequence in a column

Hi all,
I'm pretty much a noob with OO Calc.
I have the following problem....I have columns with numbers of which some are prefixed with "+" (see below example).
As you can see it's all pretty random and 90% of the time you'll have continuous runs of between 2 and 10 +numbers and vice-versa with -numbers.
Sometimes there are instances where the run of +numbers will be longer than 10.
I want to be able to search for the instances where the continuous run is longer than usual, or longer than a pre-defined amount, and also be able to count how often a longer than normal run occurs in the whole column (there are a few thousand numbers in a column).
Any tips?

+96
-232.80
-232
-212.70
-212
+100.00
+96
-216.80
-216
+100.00
+96
-198.30
-198
+100.00
+96
-207.60
-207
+100.00
+96
+100.00
+96
-230.00
-229
-273.70
-273
+100.00
+96
+100.00
+96
+100.00
+96
Last edited by Hagar Delest on Sun Feb 17, 2019 1:58 pm, edited 1 time in total.
Reason: tagged solved
Open Office 4.1.5
Windows 10
withnail

Posts: 13
Joined: Fri Feb 15, 2019 10:57 pm

### Re: How to find longest recurring sequence in a column

Hi, and welcome to the forum.

withnail wrote:Any tips?
Count consecutive positive numbers

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Volunteer

Posts: 3678
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: How to find longest recurring sequence in a column

Thanks MrProgrammer.
I can follow this but 1 thing I can't seem to understand is the following....
In the example you sent, B1 has the formula =POS.NEG(A1)
Then B2 has formula =ALS(POS.NEG(A2)=POS.NEG(A1);B1+POS.NEG(A2);POS.NEG(A2)) and so on for the rest of the column.
Surely you don't have to type all that in for each cell in the whole column? How do I get this formula to repeat itself for each cell?
I told you I was a noob
Open Office 4.1.5
Windows 10
withnail

Posts: 13
Joined: Fri Feb 15, 2019 10:57 pm

### Re: How to find longest recurring sequence in a column

Copy and paste, or use the black handle on the bottom right of the active cell to drag down.

If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know

OpenOffice Spreadsheet Tutorial for Beginners with Examples

[Tutorial] Absolute, relative and mixed references
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 2630
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: How to find longest recurring sequence in a column

It is often useful to break down the problem into simpler bits using "helper columns" to do parts of the problem.

Create a helper column where you place a 1 if the adjacent number is consecutive, and a 0 if not. Sum the 1s up to a following 0 in another helper column. Search the last helper column for the largest number.

You need to read the Tutorials and the manual, and also search the forum.

You will find much useful information in the User Guides, the Writer, Base and Calc Tutorials and the AOO Frequently Asked Questions. May I suggest you bookmark the pages.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
Last edited by John_Ha on Sat Feb 16, 2019 1:29 pm, edited 1 time in total.
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer

Posts: 6592
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

### Re: How to find longest recurring sequence in a column

I've tried copying the first and second formula's all the way down but doesn't seem to work.
I'm obviously missing something....I'll have to read up a bit first, thanks!
Open Office 4.1.5
Windows 10
withnail

Posts: 13
Joined: Fri Feb 15, 2019 10:57 pm

### Re: How to find longest recurring sequence in a column

doesn't seem to work

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here. The link also contains information on how to anonymise your document if it contains confidential information.
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 2630
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: How to find longest recurring sequence in a column

Sorry!
I've attached my example file, that's as far as I can get. When I try to copy/paste into the whole column I get an error.
I want to count the longest consecutive run of positive numbers and also quickly find the location of that longest run.
It would be nice if I could define a number/limit of say 10, and just search for any consecutive runs longer than 10.
Attachments
Geen titel 1.ods
Open Office 4.1.5
Windows 10
withnail

Posts: 13
Joined: Fri Feb 15, 2019 10:57 pm

### Re: How to find longest recurring sequence in a column

What happened when you tried using helper columns?
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer

Posts: 6592
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

### Re: How to find longest recurring sequence in a column

To be honest I don't even know what a helper column is....had a quick look at the tutorials but obviously it's a steep learning curve.
I'm not really a spreadsheet user, I just need to do this one thing but it's a bit beyond me
Apparently my neighbour is some sort of accountant and may be able to help me out, so I can stop bothering you guys haha
Open Office 4.1.5
Windows 10
withnail

Posts: 13
Joined: Fri Feb 15, 2019 10:57 pm

### Re: How to find longest recurring sequence in a column

The learning curve is very shallow, but you have to learn the basics before you can use this tool set.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 26631
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: How to find longest recurring sequence in a column

It's working, thanks everyone. It was the copying down to all the other cells where I was going wrong....
I have 1 more requirement which I can't seem to find anywhere on the forum.
I have another spreadsheet with positive and negative numbers in A1 but it's roughly 90% negative numbers and 10% positive.
For example, if there were a 1000 numbers in total with 900 negative numbers and 100 positive, then the ratio would be 1 in 10 if you like.
The positive numbers appear in random order. I want to be able to see if the positive numbers appear anywhere in "clusters", so if the total ratio was 1 in 10, then a run of say up to 3 in 10 would be acceptable, but if there were instances of 4 or more positives within a run of 10 numbers then I want to know about it.
Sorry if my explanation isn't completely clear...
Open Office 4.1.5
Windows 10
withnail

Posts: 13
Joined: Fri Feb 15, 2019 10:57 pm

### Re: How to find longest recurring sequence in a column

If the numbers are in Column A use Column B as a "helper column" where you place a 1 if the number is +ve and a zero if -ve. You can now work out how to do the rest ...
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer

Posts: 6592
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

### Re: How to find longest recurring sequence in a column

Haha, you'd think I'd know how to do the rest eh?
In this case I suppose you'd say I'm looking for an average within a specific range, instead of looking for consecutive numbers as in my original question. I want to know when that average becomes higher than a specified amount.

Was just thinking about it a bit more, the negative numbers are always -100, the positives are always different, i.e., not 100. If that would make it easier....?
Open Office 4.1.5
Windows 10
withnail

Posts: 13
Joined: Fri Feb 15, 2019 10:57 pm

### Re: How to find longest recurring sequence in a column

With John_Ha's suggestion of the helper column and contents of 1 and zero, you could then use the old fashioned Mk1 eyeball to detect clusters of 1s.
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 2630
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: How to find longest recurring sequence in a column

Aaaahh, i see
Trouble is that's what I'm trying to automate....got spreadsheets with about 40,000 numbers in a column, it's making my eyes water trying to do it manually.
My life would improve by about 80% if I could get it to count itself haha
Open Office 4.1.5
Windows 10
withnail

Posts: 13
Joined: Fri Feb 15, 2019 10:57 pm

### Re: How to find longest recurring sequence in a column

A possibility is to set the helper column to increase the number displayed when there is a change in the sign of the number in column A.
Doing it that way, you could use the COUNTIF function with the number displayed in the helper column to determine how many entries until the sign changed.
Writing this from my phone, so don't have access to write up the actual code and post an example, but will do when I can....unless someone beats me to it.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer

Posts: 5007
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: How to find longest recurring sequence in a column

Thanks RussellB, that sounds like just what I need
Open Office 4.1.5
Windows 10
withnail

Posts: 13
Joined: Fri Feb 15, 2019 10:57 pm