functionexcel-formularostering

21 days ON & 7 days OFF rotation through out the year using MOD function

I'm using IF + MOD function to return 1 or 0, it is supposed to be 21 cells with 1 followed by 7 cells with 0 but the result is not as expected. A1 has 21 then A2 has 7 to simulate the 21/7 days rotation. I put this formula =IF(MOD(ROW(A21),$A$1+$A$2)>=$A$2,1,0) to B1 and dragged down, the result is 7 consecutive cells of 1 followed by 7 cells with 0, 21 with 1 then 7 with 0. The rest are constant cycle of 21 and 7. What am I missing on this formula and what are the limitations if I need to use this on rows rather than columns or in multiple rows/ columns?

Solution

  • You could modify your formula like that:

    =IF(MOD(ROW(A1)-1,$A$1+$A$2)>=$A$1,0,1)

    then it print correctly a series of 21 times 1 and 7 times 0 in loop