Ask Your Question

Calculate the difference between two cells

asked 2017-08-24 21:22:33 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-08-24 23:40:35 +0100

librebel gravatar image

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.

edit flag offensive delete link more


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


simontfs gravatar imagesimontfs ( 2017-08-25 21:56:15 +0100 )edit

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.

librebel gravatar imagelibrebel ( 2017-08-25 22:08:41 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-08-24 21:22:33 +0100

Seen: 1,444 times

Last updated: Aug 24 '17