The number of weeks that have a certain value each day

English
#1

Let’s say i have this situation:
sheet.ods (21.1 KB)
Is it possible to get the number of weeks (a week is 7 consecutive days) that have the value 0 each day? I mean, if there are are 8 consecutive days, it would be 1 week, not 2, and to count the next week the one already calculated must be excluded.
In the file uploaded, the result should be 3, not 6.
Is it possible? Thank you.

#2

ask132731.ods (22.5 KB)

#3

@Villeroy
That’s the issue, you got 6 weeks, not 3, the ones already calculated are not excluded.
I guess it’s not possible to write a function for a whole year, right?

#4

In @Villeroy’s file, use =AND(C2;MOD(C2;7)=0) in D2.

#5

@mikekaganski
Now, do i have to this separately for each column, or is there a way to do it for a column at once?
I have 55 columns.

#6

Calc has 16000+ columns - enough for the task.

I can imagine an array formula - very complex one - which would be undebuggable, and therefore unmaintainable. My advice would be - use cells and columns, that allow you to find errors when they appear.

#7

And you can use >Data>Group and Outline for any group of helper columns. You can then hide these groups to make things “pretty” and make them shown again with a simple click.
Just for fun I created an example not needing helper columns. It requires V24.8 or higher. (First time that I actually used XMATCH() I think.
disask_132731_JustForFun.ods (35.3 KB)

#8

Thank you, this is useful; i can’t mark two posts as solution.

#9

Here is a formula that works, when you have your data in C2:C100:

=SUMPRODUCT(QUOTIENT(FREQUENCY(IF(C2:C100=0;ROW(C2:C100));IF(C2:C100<>0;ROW(C2:C100)));7))

The problem of this formula is, that it doesn’t handle blank cells (so e.g. it can’t include a whole column, or at least more rows than actually present data).

Its idea is: use FREQUENCY to obtain “an array of count of elements in each range between non-0 values”, then divide each count by 7, and sum the resulting whole numbers. ROW is used to make the “equal-to-0” elements belong to own range. Hopefully others could improve it further, to handle blanks.

#10

I just tried (still based on XMATCH). Your judgement?
disask_132731_JustForFunEnhanced.ods (44.0 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.