# How to autofill value on Google Sheets

I have the following example here.

I have a random value that is no spaced exactly, (some have 10 empty between them other 30). I would like to have some value equally added in the empty spaces.

I do not care If I have to repeat the operation 40 times because the values are not spaced correctly, but I can't find a good function that does what I would like to do.

• could those number in between be random or you want them to be sequential? – user0 Jan 6 at 2:31
• sequential like the exemple – Bobby Jan 6 at 3:06

• go to File
• select Calculation
• turn on Iterative calculation
• hit Save settings button

• paste this formula into A3, A19, A34, A60, A79, A101 etc.

``````=IF(INDIRECT("A"&ROW()-1)=INDEX(FILTER(INDIRECT("A"&ROW()&":A");N(INDIRECT("A"&ROW()&":A")));1;1);
ARRAYFORMULA(TEXT(TRANSPOSE(SPLIT(REPT(INDIRECT("A"&ROW()-1)&";";COUNTA(ARRAYFORMULA(INDIRECT("A"&
ROW()-1)+(ROW(INDIRECT("A"&ROW()&":A"&INDEX(FILTER(IF(N(INDIRECT("A"&ROW()&":A"));ROW(INDIRECT("A"&
ROW()&":A")););IF(N(INDIRECT("A"&ROW()&":A"));ROW(INDIRECT("A"&ROW()&":A"));));1;1)-1))-2))));";"));
"##"));ARRAYFORMULA(TEXT(INDIRECT("A"&ROW()-1)+(ROW(INDIRECT("A"&ROW()&":A"&INDEX(FILTER(IF(N(
INDIRECT("A"&ROW()&":A"));ROW(INDIRECT("A"&ROW()&":A")););IF(N(INDIRECT("A"&ROW()&":A"));ROW(
INDIRECT("A"&ROW()&":A"));));1;1)-1))-2)* (INDEX(FILTER(INDIRECT("A"&ROW()&":A");N(INDIRECT("A"&ROW(
)&":A")));1;1)-INDIRECT("A"&ROW()-1))/(ROWS(INDIRECT("A"&ROW()&":A"&INDEX(FILTER(IF(N(INDIRECT("A"&
ROW()&":A"));ROW(INDIRECT("A"&ROW()&":A"));); IF(N(INDIRECT("A"&ROW()&":A"));ROW(INDIRECT("A"&ROW()&
":A"));));1;1)-1)))* (ABS(MINUS(INDIRECT("A"&ROW()-1);INDEX(FILTER(INDIRECT("A"&ROW()&":A");N(
INDIRECT("A"&ROW()&":A")));1;1)))-(ABS(MINUS(INDIRECT("A"&ROW()-1);INDEX(FILTER(INDIRECT("A"&ROW()&
":A");N(INDIRECT("A"&ROW()&":A")));1;1)))* (100/(((COUNTIF(INDIRECT("A"&ROW()&":A"&INDEX(FILTER(IF(
N(INDIRECT("A"&ROW()&":A"));ROW(INDIRECT("A"&ROW()&":A")););IF(N(INDIRECT("A"&ROW()&":A"));ROW(
INDIRECT("A"&ROW()&":A"));));1;1));"<>""")+1)-1)* 100))))/ABS(MINUS(INDIRECT("A"&ROW()-1);INDEX(
FILTER(INDIRECT("A"&ROW()&":A");N(INDIRECT("A"&ROW()&":A")));1;1)));"##")))``````

• if there is a need to add decimals for ultimate accuracy, then edit `"##"` into `"##.###"`
• auto-filled cells are in `TEXT()` format so if you want to work with A column you will need to convert it into numbers with `VALUE()` - for example something like `=ARRAYFORMULA(VALUE({A7;A8;A9}))`