Stopping a column calculation with a variable start date, and restarting after 9 rows in Excel

I have a column of calculations in Excel that I want to run for 81 rows, then not calculate for 9 rows, then continue calculating for 81 rows etc. The problem I have is that the start of the column calculations varies dependant on the start date, which could be any day.

I am stopping the calculations using SUMPRODUCT function to count the NonBlank cells =SUMPRODUCT(--(LEN(C10:C90)<>0))=81,"",??????????,C$8/2) but I am struggling to work out how to restart it again and continue after 9 rows as shown below. Any help much apprciated ;0)

For older Excel here's two options:

Option 1 Create a list of dates that skip 9 after every 81st row

This can be accomplished by the following formula in your start cell of the date range:

=DATE(2023,2,11)
 +ROW()-1
 +(INT((ROW()-1)/81)*9)

If this is used in the first row of column A (or any different column) you can drag this down and after every 81st row it'll skip 9 numbers and continue from there.

If you plan on not using the first row, the -1 needs to be amended (for both cases).

In this case you don't need to calculate the value in column B based on the dates it should skip, because we just accomplished that.

It sums the start date + the current row number minus 1 and adds the integer of the current row number minus 1 divided by 81. The result of the integer (0 for the first 81 rows, 1 for the next 81, etc..) is multiplied by 9.

Option 2 Check against a list of dates that skip 9 after every 81st row

Note, this requires an array formula and option 1 is probably more advisable.

If we want a full list of dates in column A and calculate the value for 81 rows, then skip 9 and repeat, we can use the following formula in B1:

=IF(ISNUMBER(MATCH(A1,
                   DATE(2023,2,11)
                   +ROW($1:$100)-1                
                   +INT((ROW($1:$100)-1)/81)*9,
                   0)),
    $C$8/2,
    "")

This is an array formula and requires being entered with ctrl+shift+enter.

In this case we need to hardcore the number of rows (dates) you need to use. If you want to expand the number of dates you need to change 100 in ROW($1:$100) (both places where it's used in the formula) don't forget the $ in front to lock the value when dragging the formula.

This formula checks if the date value in column A exists in the date array created inside the array formula. If it does, it calculates $C$5/2 else it shows a blank value.

If we use Office 365 the date sequence can be created using:

=LET(start,  DATE(2023,2,11),
     repeat, 3,
     days,   81, 
     skip,   9,
TOCOL(start+SEQUENCE(repeat,days,0)+SEQUENCE(repeat,,0,skip)))

This spills in one go.

Or the date checker using:

=LET(start,  DATE(2023,2,11),
     repeat, 3,
     days,   81, 
     skip,   9,
IF(ISNUMBER(XMATCH(A1:A100,
                   TOCOL(start+SEQUENCE(repeat,days,0)+SEQUENCE(repeat,,0,skip)))),
   C8/2,
   ""))