Google-sheets – Arrayformula with in column operations

google sheets

I'm usign ARRAYFORMULA like this in my C1 cell:

=arrayformula(IF(row(A:A)=1; "C label"; (A:A)/(B:B)))

in my top cell and everything works as expected.

|   A   |   B   |   C label   |
|-----------------------------|
|  10   |   5   |      2      |
|   8   |   4   |      2      |
|   4   |   2   |      2      |
|  16   |   2   |      8      |

Now I need something like this:

|   A   |   B   |
|---------------|
|  100  |  100  |
|  200  |  140  |
|  340  |  160  |
|  500  |       |

that is: B2 = A3-A2, B3 = A4-A3 and so on…

How to do it automagically with arrayformula? I can't use B:B or A:A like operations in row, I can't use 1:1 or 2:2, how to?

I'm using arrayformula because of automagically calulating when adding new lines.

Best Answer

Formula

=ArrayFormula(Array_constrain(A2:A-A:A,COUNT(A:A)-1,1))

Explanation

Use Array_constrain to avoid filling the last row and filling up blank rows.