## Count consecutive positive numbers

Discuss the spreadsheet application

### Count consecutive positive numbers

There is a column with positive and negative numbers in random order. I would like to count the maximum streak of positive numbers and a maximum streak of negative numbers. For instance:
10
-5
2
56
-45
-50
-55
-32

MAx positive streak is 2, max negative is 4.
OpenOffice 3.1 Windows 7
Sheen

Posts: 19
Joined: Mon Jul 24, 2017 10:22 am

### Re: Count consecutive positive numbers

Sheen wrote: I would like to count the maximum streak of positive numbers and a maximum streak of negative numbers.
201812121141.ods

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

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

### Re: Count consecutive positive numbers

Use one (or two) helper column for checking if the actual and the previous number has same sign or not. Flag and count the same negative number pairs with a negative counter, and flag and count the same positive number pairs with a positive counter. The different sign will reset the counters.

Finally you can search the MIN() The negative number with large absolute value, and the MAX() the positive number with large absolute value in the helper column/s/.

(SAME as suggested by MrProgrammer. I am too slow)
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 7972
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Count consecutive positive numbers

MrProgrammer solution is the best and easiest solution, but this can be done whithout helper.
This is an array formula, entered with Shift+Ctrl+Enter.

Code: Select all   Expand viewCollapse view
`=MAX(FREQUENCY(IF(A1:A8>0;ROW(A1:A8));IF(A1:A8<0;ROW(A1:A8))))`
LibreOffice 6.2.4 on Windows 10
gerard24
Volunteer

Posts: 942
Joined: Sat Oct 30, 2010 5:12 pm
Location: France