I am new to the formula side of Google Sheets and am trying to get each row to add 1 to the previous row ex. A2=A1+1, A3=A2+1. I know I can use =A1+1 and drag it down, but I want it to continue if a row is added in between. Can an array formula work?
I tried using =ARRAYFORMULA(A2:A,A1+1) but it says there is circular dependency. Why is that?
Richard Wise is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Understanding Circular Dependency in Google Sheets Formulas
The formula you have been trying =ARRAYFORMULA(A2:A, A1+1) creates a circular dependency because it references the output range (A2) while trying to calculate new values. ARRAYFORMULA needs a single operation to produce an array, and this structure doesn’t work.
As a workaround, please try this Google Sheets formula:
=ARRAYFORMULA(ROW(A2:A) + A$1 - 1)
This creates a sequential list of numbers starting from 2 in cell A2 and adding the value of A1 and adjusting for the row index. With this, you can freely add rows in between, or add new rows.
OUTPUT:
NOTE: The value of cell A1 which is 1 is manually entered.
REFERENCES:
- Circular Dependency
- Arrayformula
- ROWS
You can use SEQUENCE() function.
=INDEX(A1+SEQUENCE(ROWS(A2:A)))
Use sequence(). Put the start value in cell A1 and this formula in cell A2:
=sequence(999, 1, A1)
That will fill down to row 1000. To fill the entire column down to the bottom, regardless of how many rows there are, use rows(), as shown by Harun24hr’s answer.
See sequence().
As an alternative, you can use the SCAN function;
=SCAN(0,A1:A,LAMBDA(acc,xVal,acc+xVal+1))
2