Count consecutive positive numbers

Discuss the spreadsheet application

Count consecutive positive numbers

Postby Sheen » Wed Dec 12, 2018 6:48 pm

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

Postby MrProgrammer » Wed Dec 12, 2018 7:46 pm

Sheen wrote: I would like to count the maximum streak of positive numbers and a maximum streak of negative numbers.
201812121141.ods
(10.56 KiB) Downloaded 27 times

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

Re: Count consecutive positive numbers

Postby Zizi64 » Wed Dec 12, 2018 7:53 pm

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.
User avatar
Zizi64
Volunteer
 
Posts: 7974
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Count consecutive positive numbers

Postby gerard24 » Wed Dec 12, 2018 10:10 pm

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


Return to Calc

Who is online

Users browsing this forum: Lupp and 27 guests