Google Spreadsheet, operations with above row cell in same column with arrayformula

Question

I have arrayformula in the first row of a column so my values and calculations can start in Row 2 and for all the column length.

I have this situation:

https://docs.google.com/spreadsheets/d/11oDra7Vja4-5C0Uix7JTgLLSMG3gPj-6fkajXlWqqQk/edit?usp=sharing

I need a simply arithmetic operation:

Subtract above value of the same column for every row.

I'm using:

=arrayformula(IF(row(A:A)=1; "What I have now"; IF(ISBLANK(A:A); ""; A1:A-A2:A)))

but as you see is wrong.

How to do that?

UPDATED QUESTION:

And then in the second sheet I need a SUM operation with some blank cells in column:

How to do that?

https://docs.google.com/spreadsheets/d/11oDra7Vja4-5C0Uix7JTgLLSMG3gPj-6fkajXlWqqQk/edit#gid=931743679

Answer 1

If you want to have the array formula ion the header this is a bit weird as you need to allow the formula to technically access row 0, we can do this by constructing ranges.

=ArrayFormula(IF(
  --(ROW(A1:A) > 2) + -ISBLANK(A1:A) = 1; 
  {0; A1:A} - {0; A2:A; 0}; 
  ""))

--(ROW(A1:A) > 2) + -ISBLANK(A1:A) = 1 Checks if the row is populated and not one of the first two rows in a way that works nicely with array formulas

{0; A1:A} - {0; A2:A; 0} does the following:

0   Data   156    123   110    95    42
-   -     -      -     -     -     -
0   156    123    110    95    42     0
=   =     =      =     =     =     =  
0    33     13     15    53    42    42
N     N      Y      Y     Y     Y     N <- Is shown  
^     ^                               ^
|     |                               Because Row is blank
|     |
Because row not > 2, thus it is never evalauated even though the second would cause an error

Answer 2

I think this is quite tricky. The problem is that in an array formula the number of cells in each array must match - you can't mix an array starting in row 1 with an array starting in row 2 if they go all the way to the bottom of the spreadsheet.

Here is one way of getting the result you want

=arrayformula({"What I need";"";offset($A$1,1,0,count(A:A)-1)-offset($A$1,2,0,count(A:A)-1)})

You will need to change the ; and , for your locale.

I have built up an array using the {} notation to define the elements. In my locale a ; means go to the next row, so I have defined the first two cells directly as strings. After that I've chosen to use Offset to get the range A2:A5 (1 row down from A1, 0 rows across and 4 cells high) and subtract the range A3:A6 (2 rows down from A1, 0 rows across and 4 cells high) it so that gives me the other 4 cells.

B1 "What I need"
B2 ""
B3 A3-A2=33
B4 A4-A3=13
B5 A5-A4=15
B6 A6-A5=53

but will need an IF statement adding if there are any blank cells between the numbers.

In the particular case of your updated question where there are fewer numbers in column D than column C, the formula would be

=arrayformula({"Special Case";"";offset($D$1,1,0,count(D:D))+offset($C$1,2,0,count(D:D))})

But in the general case of there being blank cells anywhere, you would have to test everything

=arrayformula({"General Case";"";if(offset($D$1,1,0,rows(C:C)-2)="","",if(offset($C$1,2,0,Rows(C:C)-2)="","",offset($D$1,1,0,rows(C:C)-2)+offset($C$1,2,0,Rows(C:C)-2)))})