Google-sheets – Increase 1 increment every x rows on Google Sheets

google sheets

Basically, I just want to automatically increase the week number (on column A), and have it correspond with every new Monday (in Column B). I have been trying to click and drag, hoping it would somehow automatically adjust the increment every 7 rows but it doesn't seem to work.

How would I be able to do this without having to manually increase the number every Monday?

Best Answer

Formula

=ArrayFormula(IF(WEEKDAY(FILTER(C:C,C:C>0),2)=1,WEEKNUM(C:C,1),))

Explanation

WEEKNUM(C:C,1) returns the number of the week for all the values column C. To to fill only the cells that corresponds to mondays and that row is not empty, it was included as the TRUE result of an IF function with the following test:

WEEKDAY(FILTER(C:C,C:C>0),2)=1

ArrayFormula makes that the results be expanded to the required rows.

Related Solutions

Google Sheets – Alternate Background Colors and Conditional Formatting Based on Date

If every week is represented (i.e., the dates are not skipping any weeks), then conditional formatting with the custom formula =isodd(weeknum(A7, 2)) does the job. (=iseven(weeknum(A7, 2)) works too, naturally.)

The second argument 2 is needed because your weeks are Monday-Sunday. If it's not included, weeknum uses Sunday-Saturday weeks.

If there may be some gaps, use conditional formatting with custom formula

=isodd(countunique(arrayformula(weeknum($A$7:$A7, 2))))

That is, count the number of different weeks represented in the column so far, and format if this number is odd.

As usual, the formatting is applied to the entire range such as A7:P at once; the formulas stated above will be correctly interpreted for the cells below A7, using relative references.

Google Sheets – Dividing Rows Automatically with a Stationary Cell

Short answer

Try

=IFERROR(OFFSET(E1,0,count(E1:I1)-1),0)/J1

Explanation

The desired calculation could be done by using formulas. As the scripts run on Google Servers, if you are able to do something with formulas try them first as usually they will do calculations faster than a script that does the same.

It's worth to say that you could create custom functions and do some automations by using Google Apps Script¹.

¹: Extending Google Sheets

Best Answer

Formula

Explanation

Related Solutions

Google Sheets – Alternate Background Colors and Conditional Formatting Based on Date

Google Sheets – Dividing Rows Automatically with a Stationary Cell

Short answer

Explanation

Related Topic