2

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
0
  • go to File
  • select Spreadsheet settings...
  • select Calculation
  • turn on Iterative calculation
  • hit Save settings button

9

  • 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)));"##")))

9

  • 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}))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.