How can we calculate value from previous row in Excel-CodePudding

I want to make X3 output like this

Where :

First Row of X3 is

X3 = fixed_cost - X1

but the second row is calculated by

X3 from row 1 - X1 from row 2

and also the problem is the id is not sorted (only sorted by year for each id) and the data is over 100K. Is there any solution for this case? thank you!

I have tried using this formula

=IF(COUNTIF(A$2:A2;A2)=1;C2-D2;F2-D3)

But the result is like this

Any solution? thank you

CodePudding user response：

Let say your table is like so. In cell F2, enter formula =C2-D2). this will give you F2. F3 formula will be =(F2-D3). Then copy paste F3 to F4 and so on. Hope this help. There are a lot of formula example online.

CodePudding user response：

If C column is a constant you can use:

=C2-SUMIF(A$2:A2,A2,D$2:D2)