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

Discuss the spreadsheet application

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

Postby withnail » Fri Feb 15, 2019 11:38 pm

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?
Thanks in advance :-)

+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

Postby MrProgrammer » Sat Feb 16, 2019 2:16 am

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).
User avatar
MrProgrammer
Volunteer
 
Posts: 3615
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to find longest recurring sequence in a column

Postby withnail » Sat Feb 16, 2019 1:11 pm

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

Postby robleyd » Sat Feb 16, 2019 1:21 pm

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
User avatar
robleyd
Moderator
 
Posts: 2515
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to find longest recurring sequence in a column

Postby John_Ha » Sat Feb 16, 2019 1:22 pm

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: 6467
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: How to find longest recurring sequence in a column

Postby withnail » Sat Feb 16, 2019 1:29 pm

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

Postby robleyd » Sat Feb 16, 2019 1:38 pm

doesn't seem to work

General advice: "It didn't work" isn't helpful in the forum because it tells us what did not happen. Please never use that phrase in a post. We need to know exactly what actions you took, what did happen, and what you expected to happen. Attaching a document demonstrating the problem is almost always helpful for us and will get your problem solved more quickly. If you typed a formula, pressed Enter, and got #VALUE!, tell us that. Then attach your document so we can see what's wrong. Often "it didn't work" means "it didn't do what I expected" and the real problem is that the program is behaving correctly but your expectations are incorrect. To help you solve the problem we need to know your expectations and your ultimate goal.

[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
User avatar
robleyd
Moderator
 
Posts: 2515
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to find longest recurring sequence in a column

Postby withnail » Sat Feb 16, 2019 1:57 pm

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
(10.53 KiB) Downloaded 12 times
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

Postby John_Ha » Sat Feb 16, 2019 2:05 pm

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: 6467
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: How to find longest recurring sequence in a column

Postby withnail » Sat Feb 16, 2019 2:08 pm

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

Postby Villeroy » Sat Feb 16, 2019 2:20 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to find longest recurring sequence in a column

Postby withnail » Sat Feb 16, 2019 6:27 pm

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

Postby John_Ha » Sat Feb 16, 2019 6:38 pm

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: 6467
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: How to find longest recurring sequence in a column

Postby withnail » Sat Feb 16, 2019 6:54 pm

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

Postby robleyd » Sun Feb 17, 2019 12:24 am

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. :D
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
User avatar
robleyd
Moderator
 
Posts: 2515
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to find longest recurring sequence in a column

Postby withnail » Sun Feb 17, 2019 12:29 am

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

Postby RusselB » Sun Feb 17, 2019 1:07 am

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: 4874
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to find longest recurring sequence in a column

Postby withnail » Sun Feb 17, 2019 1:18 am

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


Return to Calc

Who is online

Users browsing this forum: Lupp and 12 guests