CODEWITHSUNDEEP
excellibreoffice-calc
Wolfger
Wolfger

Reputation: 191

How to count a streak in Excel (Calc), with a changing range

I am creating a spreadsheet to track my habits through the coming year, and I want to be able to show both a simple count, and also a current streak for each habit. I've already searched and found one answer for counting a streak, but this answer does not work with a live tracker (increasing the end column by one each day), and also requires there to be no blank fields (1 or 0 entry).

=COUNTA(S$2:S$74)-MATCH(2, 1/(S2:S$74=0), 1)

If I switch the above from =0 to ="" in the match, it breaks pretty spectacularly, and in any case it fails completely for a range that includes 366 days, but the current day is <366. I would be tracking "current day" by a habit row labeled "logging" that I would populate each day, whether any other habits were logged or not. Is this something possible with formulas?

Using the suggestions I got to concat 1's and 0's to a string, I then got a successful streak formula using 

=IFERROR((LEN(NG3)-SEARCH("0[^0]*$",NG3)),LEN(NG3))

Where NG is the column the concatenations exist in, and row 3 is the first row of data. I then basically duplicated my calendar to allow for non-binary logging (converting any blank or 0 entry to a 0, all other entries to a 1), and contingent on the "logged" row being populated so that unlogged days are blank fields. Works like a charm now.

Upvotes: 1

Views: 229

Answers (1)

JGFMK
JGFMK

Reputation: 8904

My interpretation of the algorithm for this:

  1. If you had a 1 for each time same thing happened, and 0 when you did something different.
  2. Then you concatenated all the 0 and 1 into one long string.
  3. Then split the string into an array based on 0, removing all the zeros.
  4. Then finally counted the maximum length of the array result

In formula put the following:

=MAX(LEN(SPLIT(B1,"0")))
  • Then either press Shift + Ctrl + Enter on Windows
  • Or Cmd + Shift + Enter on a Mac

This will convert formula into an ArrayFormula.

(You need to do this, because if you just entered 

=SPLIT(B1)

in C1, it would generate a range of columns, one per array entry, filling C1, D1, E1 etc...).

Here are the formulae:

Array Formula Example

Here is the output:

Output from Formula

Sample spreadsheet in GoogleDocs

You may be able to upload/download/convert it between Googledocs and LibreOffice.

Have not used that spreadsheet solution.

But most are pretty similar these days.

Upvotes: 1

Related Questions