Using two values from one column to fill another column and repeat the calculation

Question

I'm trying to work out how far I travel on a tank of petrol. I've set a column to display the mileage at the time I enter the petrol station, and I want to display the difference between the two values in the next column.

Example:

Mileage is in column F and tank mileage is in column G.

I can get it to calculate the one tank, but I want Column G to auto calculate the tank mileage each time I add to the Total Mileage column.

This is how far I've got. located in Cell G3

=ARRAYFORMULA(IF(F3:F,(F3-F2:F),""))

It subtracts the value in F3 from the value in F2 to show me how many miles I've travelled on one tank of fuel. But I can't figure out how to make it work more than once.

I hope I'm making sense, I've been self-teaching myself Google Sheets all week and thanks to this site I've come this far. I'm probably over-engineering it.

Any help would be appreciated.

Answer 1

Short answer

=ARRAYFORMULA(IF(F3:F,(F3:F-F2:F),""))

Explanation

Both parameters of a subtraction operation should be arrays in order to return an array.

Answer 2

Another but better short answer

=ARRAYFORMULA(ARRAY_CONSTRAIN(F3:F-F2:F,COUNT(F3:F),1))

Explanation

The formula pattern used in the question and the other answer by myself could produce "some problems" as the rows below the last row with data in the F column will be filled with an empty string "".

Examples of "problems"

• COUNTA will return the number of cells having an empty string
• The data range will have the same number of rows that the sheet
• The recalculation time of the spreadsheet will be longer than what it's required.

Instead of using an empty string, limit the size of the resulting array. There are several ways to do this. Some of them use functions like QUERY, FILTER, ARRAY_CONSTRAIN, OFFSET, IFERROR, other could use Google Apps Script.

In the case of this question, ARRAY_CONSTRAIN was chosen because F3:F has one row less than F2:F and using FILTER doesn't allow to use F3:F as the filtering criteria and using F2:F will include and extra row in the result.