4

I have a sheet with form response data in several columns, say A:G. Some of the data are numerical and I'd like to have some calculations done on them, e.g. in columns H through J. I have already found out any stretched formulas get wiped out as new lines from the form get pulled in.

So I created a new sheet, imported the data using arrayformula, but still, I don't know how to have a formula for every row in columns H:J (except the header). I can only stretch the formula within a fixed number of rows, but don't know how to make it autoadjust to newly incoming data.

I wondered if I could use a script that would be triggered with the form being submitted, adding the formulas to the new lines, but that's rather inelegant.

Sample spreadsheet: Sheet1 contains form data, Sheet2 imports them using arrayformula.

  • Then you need to set them as an ARRAYFORMULA – Jacob Jan Tuinstra Mar 31 '13 at 18:18
  • Why don't you share a doc with us? – Jacob Jan Tuinstra Mar 31 '13 at 19:19
  • As I write in the question - I have tried arrayformula, but the underlying problem - the formula not stretching - still occurs. A sample spreadsheet is here: bit.ly/10rlsMI. Sheet1 contains form data, Sheet2 imports them using arrayformula. – Ondrej Mar 31 '13 at 21:38
  • An adaptation of a extract of the comment was added to the question but instead of the short URL, the spreadsheet URL was used in the link. This because the comments are not intended to be permanent. – Rubén Jan 16 '16 at 15:22
3

If you add the following formula in cell D2:

=ARRAYFORMULA(FILTER(C2:C;C2:C<>"")*1.2)

then it will take on the complete column, via the array. The FILTER function, is to make sure no empty cells are taken into account.

I've added the result into the file you shared with us.

  • Works like magic, for some reason :) Thanks! – Ondrej Apr 1 '13 at 13:37
0

A shorter version of the formula in the answer by Jacob Jan Tuinstra

=FILTER(C2:C*1.2;C2:C<>"")

FILTER is a function that returns an array so, if the operations are done in the first argument, the outer ARRAYFORMULA function it isn't required.

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.