formulasgoogle sheets
I'm using the following formula to create an auto increment to form responses. I need the starting value of "100000" and all subsequent values to be a number, not a string.
Formula
=arrayformula( if( len(A2:A), text(row(A2:A) - row(A2) + 1, "100000"), iferror(1/0) ) )
How can I update the above formula so it outputs a number value, not a string showing a number?
you could use a formula like:
=ARRAYFORMULA(FILTER(ROW(INDIRECT("A1:A20")), MOD(ROW(INDIRECT("A1:A20")), 2)=0))
A1:A20 represents range 1-202)=0 represents increment by 2Assuming the data starts in row 1, try in B1:
=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),{{0;FILTER(ROW(A:A),LEN(C:C)=0)},{FILTER(A:A,LEN(C:C)=0);""}},2),))
Out of interest, the formula simplifies a bit if you are retrieving from cells below:
=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},LEN(C:C)=0),2),))
Best Answer
Short answer
To numerate rows in a table, like the form responses, starting on 100000, use the following formula:
Explanation
ROW(A2:A) - 2returns a array of consecutive numbers starting on 0.+ 100000makes that the array of consecutive numbers start on 100000.LEN(A2:A)as the filtering criteria argument of FILTER, makes that only the rows with responses be numerated.