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

[withnail]

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

[MrProgrammer]

Hi, and welcome to the forum.

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

[withnail]

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

[robleyd]

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

[John_Ha]

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.

[withnail]

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!

[robleyd]

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.

[withnail]

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.

[John_Ha]

What happened when you tried using helper columns?

[withnail]

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

[Villeroy]

The learning curve is very shallow, but you have to learn the basics before you can use this tool set.

[withnail]

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...

[John_Ha]

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 ...

[withnail]

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....?

[robleyd]

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.

[withnail]

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

[RusselB]

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.

[withnail]

Thanks RussellB, that sounds like just what I need