# How do I get a formula copied to all cells below it in the entire column and increment by x instead of manually dragging it down to eternity?

If in Cell `A2` I have the formula,

``````=IF(MOD(ROW(),1)=0,A2+1,A2)
``````

how do I get it copied to all the cells below in the entire column (e.g. 900'000 cells) instead of manually dragging it down to eternity?

• Your first formula trigger a an infinite loop (Iterative calculation) so 'im not sure this `=ArrayFormula(IF(MOD(ROW(),1)=0,A2:A10+1,A2:A10))` can solve the problem. Second formula, i'm not sure what you're looking for, can you explain a bit more ?
– pjmg
May 16, 2020 at 12:43
• This is my original question, so you have an idea of what I'm looking to achieve. webapps.stackexchange.com/questions/142367/… May 16, 2020 at 13:59

You can do that using the following arrayformula in cell `E2`:

``=ArrayFormula(INT((ROW(A2:A)-1)/9))`` ### How the formula works:

First of all you need to make sure you place it in the correct row. That is if your range starts at the second row (like `E2-E`) row you place it in the second row. As you notice in the given formula the column is `A`. That is to show you that the column makes no difference. It could be any column. It is the row that is important.

Then we fix the step which is one above the one we want. In this case in order to have 8 repetitions, we divide by 9.

Finally, we can control the starting number by adding a number in the last part of the formula.In this case we start from zero so we add nothing.
But in columns `B` and `C` we start from 1 and 2 so we use:

in column `B`

``````=ArrayFormula(INT((ROW(B2:B)-1)/9)+1)
``````

and in column `C`

``````=ArrayFormula(INT((ROW(C2:C)-1)/9)+2)
``````

Functions used:

• Please remember that you can also upvote an answer so others can benefit as well. May 16, 2020 at 14:55