Calculate the difference between two cells

[simontfs]

In cell 1A value = 100
in cell 2A value = 200
In cell 3A value = 300
..and so on
Difference amount goes into 1B
So in this case 1B = 200   (3A-1A)

Without knowing how many entries will be introduced in column A (could be 10 or could be 110 entries)
How can I ask calc to calculate the difference between the last entry in Column A and the first Entry in Column A (1A)

So Basically I just want to subtract the first entry from the last current entry In Column A with out out knowing how many entries there might be.

Thanks for the help

[librebel]

Hello @,

please try the following Formula in cell B1:
( this is an Array Formula; when entering this formula in the Formula Editor, do not include the accolades {} and make sure to check the checkbox “Array” ):

{=OFFSET($A$1;MAX(IF($A:$A<>"";ROW($A:$A);0))-1;0;1;1) - $A$1}

NB. The notation $A:$A means the whole column A, in older versions it used to be written as $A$1:$A$1048576.

[simontfs]

That is perfect just what i needed thankyou very much. Just A quick edit. If I wanted to apply the same formula to C column and output to D1 would it be

=OFFSET($C$1;MAX(IF($C:$C<>"";ROW($C:$C);0))-1;0;1;1) - $C$1

Thankyou.

[librebel]

You’re welcome @simontfs,
i guess it would work in column C too, tho i have not tested that.

To accept the answer as correct, please click on the small checkmark icon on the Left.