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 another solution wich suited my needs and is not mentionned here to the problem of calculating the difference between a value and its previous one in the same column without having to manualy copy (aka: using arrayformula function).

Supposing your values lie in column B and start at row 3: ARRAYFORMULA(IFERROR(IF(ARRAY_CONSTRAIN(B3:B,COUNTA(B3:B),1)>ARRAY_CONSTRAIN(B2:B,COUNTA(B3:B),1),ARRAY_CONSTRAIN(B3:B,COUNTA(B3:B),1)-ARRAY_CONSTRAIN(B2:B,COUNTA(B3:B),1),NA()),NA()))

The key thing here is the use of ARRAY_CONSTRAIN([needed_range_for_calculation],[number_of_non_blank_values],1) instead of filtering (wich is, I think, resources hungrier and less readable).

The formula above returns a #N/A value in case of error (eg: NaN in B2) or if the previous value is greater than the current value. If negative differences are allowed, it would become: ARRAYFORMULA(IFERROR(ARRAY_CONSTRAIN(B3:B,COUNTA(B3:B),1)- ARRAY_CONSTRAIN(B2:B,COUNTA(B3:B),1),NA()))

In the case your formula is written in the first row, the solution in spreadsheet posted by @user3912079 of using IF(row(B:B)=1;NA();([your formula])) to check if it is the first line should work fine.

Answer 2

Actually I don't really understand why don't you simply write to the "E2" cell:

D2-D1 (assuming that your values are in the D column), and then just drag the formula down, until you have values. (I understand that you'll have more and more values in the D column, but you can add an if condition to check whether that particular D cell is empty or not, and if not then calculate the substraction from the previous row.)

Maybe that's not what you are looking for (then sorry, I misunderstood the question), but anyway I would do it this way.

Hope it helped.

UPDATE:

After understanding the real problem, I would use this function:

=arrayformula(if(isblank(A3:A100),"",filter(A3:A100,A3:A100>-1)-filter(A2:A99,A2:A99>-1)))

Supposing that your data is in the A column (and no value in the A column is smaller than 0!), write this to cell B3, and it will produce the diff-s.

Explanation: Look at the last part of the function - ...filter(A3:A100,A3:A100>-1)-filter(A2:A99,A2:A99>-1) - This is simply two queries which will give back two equal size list. The first one is going from A3-A100, the second one goes from A2-A99. It's essential that the elements in the two lists should be equal. (In this case 98 elements are in both lists). And there is a substraction (-) sign between the two list, so if we put ARRAYFORMULA before these two lists it will do the substraction for every "pair" in the lists. First element of the lists are "A3" and "A2", so A3-A2 will be the value of B3. Second elements of the list are "A4" and "A3", so A4-A3 will go to B4, and so on. So basically we're done:

=arrayformula(filter(A3:A100,A3:A100>-1)-filter(A2:A99,A2:A99>-1))

The main part is done, although an extra IF serves the purpose that if the current A field is blank then we write an empty string (""), otherwise we compute the substraction, mentioned above.

So the full function again:

=arrayformula(if(isblank(A3:A100),"",filter(A3:A100,A3:A100>-1)-filter(A2:A99,A2:A99>-1)))

As I said earlier, this function assumes that there are only non-negative numbers in column A. If that's not the case, simply rewrite the filter condition (-1) to a very small NEGATIVE number. (like "-99999999")

Hope it helps.

Answer 3

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.

Answer 4

Am I late? I arrived also to array formula looking at next line. Why not look at value next line in an aray too, so you have both the new value and the difference in the same line.

https://docs.google.com/spreadsheets/d/1mR2zI1KKqBtj0LGgO_keWyVbWRqotsLTiak40BNXI8E/edit?usp=sharing

Comment: I have used these arrayformulae that look to the next line in two places. In both when creating the formula, the sheet goes to more than 30000 lines, but hey can be deleted and everytihing goes ok.