Google Sheets - ArrayFormula Calculate Difference with Previous Cells

Question

Using a Google Spreadsheet, I am wanting to be able to put a formula into the header column so that I don't have to manually fill rows (I have a Google Script which is adding values automatically each day). I understand that a common way to do this is using ArrayFormula.

I have a few formulas which I want to setup like this, but for now I'm trying to get a difference between the current cell and the previous row's cell going.

This is what it should be doing:

Bal Dif 0 20 20 20 0 21 1 22 1

The closest I can get with an ArrayFormula is this though:

Bal Dif 0 20 20 0 20 1 21 1 22 1

This is using the formula: =arrayformula(D2:D-D1:D). Its almost as if I need =arrayformula(D1:D-D0:D) but obviously this isn't valid.

Any nice ways to get the difference value on the line which actually has the new value?

Thanks, hope someone can help.

Answer 1

I found the previous answers a bit complicated.

Assuming, A2:A contains the data, the following is much more brief:

=ARRAYFORMULA(IF(A2:A="", "", IF(ROW(A2:A)=ROW(A2), "", A2:A-A1:A)))

This generates the following table:

Bal	Diff
0	##Formula here##
20	20
20	0
21	1
22	1

Explanation:

The first IF with (A2:A="") avoids filling the entire column. It also avoids the expansion of the sheet. Without that check, the sheet grows indefinitely in the background. It triggers an error when page size hits the limits.

The second IF with ROW(A2:A)=ROW(A2) handles the first data row, if we need something else in the first row, we may change this line.

The last part (A2:A-A1:A) calculates the difference. In most other calculations, Google sheets complain about the size mismatch in the ARRAYFORMULA definition. In that case, it just works fine, although the size of A2:A is less than A1:A.

If we are allowed to put the formula at B3, the formula can be simpler:

=ARRAYFORMULA(IF(A3:A="", "", A3:A-A2:A))

Note: For the people who know the IFS statement, it does not work here. The IFS formula shall fail with the size mismatch error.