Make Google Spreadsheet Formula Repeat Infinitely

Question

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 1

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.