Make Google Spreadsheet Formula Repeat Infinitely

google-sheets google-forms array-formulas
slister picture slister · Sep 27, 2013 · Viewed 54.8k times · Source

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.

Answer

Brionius picture Brionius · Sep 27, 2013

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.

Related questions

Is it possible to 'prefill' a google form using data from a google spreadsheet?

I'm looking for a way to 'pre fill' a google form with specific data from a google spreadsheet. The form will have the same 'standard' questions for everyone, but the data in the first two question will be 'prefilled' with …

Read More
Retrieve the e-mail address of a Google Form respondent without asking for their e-mail address in the form

The scenario goes: Via email, a Google Form is sent to a respondent. Via this sent email, the respondent answers the form and sends their response. This form has no email address field. I need to retrieve the respondents e-mail …

Read More
Hidden field in a Google Form

I am adding a unique ID to each submission of the form. Right now, I made that ID the first field, and pre-filled it with the ID, along with a help text asking the user not to modify this field. …

Read More