What's the best way to grab a value from a prior row in a calculated column?

[Dan Kellem]

Given a data set like this:

Group Index Data Calc

A 1 a 0

A 2 b b-a

A 3 c c-b

A 4 d d-c

A 5 e e-d

B 1 f 0

B 2 g g-f

B 3 h h-g

B 4 i i-h

B 5 j j-i

For a given row (like Group A, Index 3), how would you create a calculated column as the difference from the data in that cell © with the data in the same Group, but prior Index (b)? The first row in each group is handled easily with a case statement.

Spreadsheet w/ data above attached.

Thanks!

[Dan Kellem]

The solution:

Add a calculated column to grab the value from the previous row:

[Data] - first([Data]) over (intersect([Group],Previous([index])))

Then add another calculated column with a CASE statement to handle the 0 on rows with Index = 1.