Saturday, January 28, 2012

yogi_Apply Formulas To FormSubmittals Involving Previous Row Values

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
user VTXMan said:
Apply formulas to form-inserted rows
I am trying to make a form/spreadsheet to track my fuel mileage. I have the form built and it populates the data nominally. However, the formulas for calculations I perform on each entry do not get added to the new rows.
I have searched and found about 100 threads all saying to use ArrayFormula(). So, I have changed my formula from =(E3-E2) that calculates the miles driven, to =ArrayFormula(IFERROR((E3-E2))).
This formula properly calculates the field on the rows already in the sheet, but when the form adds a row, the formula doesn't copy down the column to become =ArrayFormula(IFERROR((E4-E3))). The new field is just blank.
What am I missing?
following is a solution to the problem

here is the Form that I used to populate the Form Responses sheet

and here is the Summary of response