Get Cell Difference in Array SCAN/LAMBDA

[MrRajKumar]

Hi,

The following are the data in A1:A5

2​

3​

6​

8​

20​

In B1:B5, I would like to get their difference in spill format. If we do this manually, it will be;

B1: =A1
B2: =A2-A1
B3: =A3-A2
B4: =A4-A3
B5: =A5-A4

So the final result should be

2
1
3
2
12

I tried this formula, but doesn't work.

=SCAN(0,A1:A5,LAMBDA(x,y,y-x))

 

[Cubist]

SCAN acts as an accumulator. What it's doing is:
3-2 = 1
6 - 1 = 5
8 -5 = 3 and so on ...

It subtracts from the prior result not the prior cell. Try this.

Book2
	A	B	C
1	2	1	1
2	3	3	3
3	6	2	2
4	8	12	12
5	20
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=MAKEARRAY(ROWS(A1:A5)-1,1,LAMBDA(r,c, INDEX(A1:A5,r+1)-INDEX(A1:A5,r)))
C1:C4	C1	=A2:A5-A1:A4
Dynamic array formulas.

 

[MrRajKumar]

Thank you. I used the 2nd method with the help of TAKE/DROP formulas.