32

Okay so I have a Google Form that dumps info into a spreadsheet. On each line I need to have a simple calculation done. The problem is I can't figure out how to get it to repeat a formula on every new line as new lines are added.

Yes I know how to use the fill handle to copy formulas down and what not, but I want it to automatically add the formula instead of me manually copying it.

For example this is being used to track time so there is a cell for In Time and a cell for Out Time on each row. I want to have a column called Time Spent that will subtract their in time from the out time to determine how much time they spent. But since there are an infinite number of rows it is not practical for me to go in and copy the formula.

If anybody has any ideas I would really appreciate it. I have been looking around for ages and all I can ever find is people saying to use the fill handle to copy formulas down manually which is not what I want.

Rubén
  • 34,714
  • 9
  • 70
  • 166
slister
  • 769
  • 1
  • 13
  • 29

2 Answers2

60

Let's say the In Time cells are in Column A, and Out Time cells are in Column B, and you want Time Spent to be in Column C. Put this formula in cell C2 (assuming A1, B1, and C1 contain headers, not data):

=ARRAYFORMULA(B2:B - A2:A)

The ARRAYFORMULA function instructs the spreadsheet to iterate the contained formula over the ranges given, and a reference without a final number like B2:B refers to a range that contains all the remaining rows in the spreadsheet.

Brionius
  • 13,858
  • 3
  • 38
  • 49
  • 2
    Thank you so much that is exactly what I needed. I have been searching for that for months now and could never find a straight answer. One more question since Array Formula syntax still trips me up a bit how would I implement an if statement in there. For example I would want something like IF(B2 = "?","?",B2-A2) – slister Sep 27 '13 at 12:31
  • 3
    Never mind I figured it out =ARRAYFORMULA(IF(F2:F="?","?",F2:F - E2:E)) Thanks so much for pointing me in the right direction. – slister Sep 27 '13 at 13:16
  • @Brionius how can i apply =HTTPResponse formula for entire column for old and new data ? arrayformula will not work for =HTTPResponse! . i don't want to copy the formula manually for entire column! – user1788736 Oct 04 '15 at 17:02
  • 1
    For folks who are interested, I put together an [example sheet](https://docs.google.com/spreadsheets/d/1xbuqwfkyMLFgvMx_AWDH6XpjR12A8ZT6ag4wugEh85s/edit?usp=sharing) – mbigras Mar 07 '17 at 04:46
  • This was exactly what I needed. I can't believe the docs don't have this example. You make a range where there would normally be just one cell. So =ARRAYFORMULA(IF(A2:A, A2:A + B2:B, "")) – Brandon Keith Biggs Sep 19 '17 at 11:15
  • @Brionius What do I use if the times values is all in one column? Like if I want something like "=B3-B2" to be automatically filled endlessly in column C? – Daniele Testa May 16 '20 at 21:41
  • @DanieleTesta In cell C1 enter something like this formula: `=ARRAYFORMULA(B2:B - B1:B)`. That would make column C equal to the difference between consecutive cells of B. It's a little odd, since technically B2:B and B1:B don't have the same # of cells, but Google helpfully allows that when there's no end row number. – Brionius May 18 '20 at 15:39
  • It works fine for me...are you sure you aren't putting `=ARRAYFORMULA(B2:B - B1:B)` in column B? Because that would give a #REF! error. At this point you should just ask a separate question so we don't have to do this in the comment thread. – Brionius May 22 '20 at 02:49
8

If for some reason you find arrayformula tricky to use in your situation, an alternative option is to create a mirror of the table on another sheet using a query that has the formulas written into it.

E.g. if you have numbers in columns A and B and a formula to add them together:

=query(Data!A:B, "select A, B, A + B")

The benefit to this approach is that you'll often want to do aggregation and summarization on your form data, and you can kill two birds with one stone by doing that in the same query that you use to apply the formulas.

I'll mention one other trick that has been useful in the past when Google's query syntax has a gap in functionality. You can actually apply an array formula to the data before passing it in to the query function, like this following example, which has output equivalent to the previous query.

=query({Data!A:B, arrayformula(Data!A:A+Data!B:B)},
  "select Col1, Col2, Col3")

Note how the columns are no longer referred to by letter, but by Col1, Col2, etc.

Tmdean
  • 9,108
  • 43
  • 51