Create Calculated Field that Subtracts Field 1 from Field 2 (within same Column)

[felixcaspari]

I would like to create a calculated field (as displayed in column E) that locates the lowest system ID (column B) within a project ID (column A) and populates "E3" with the number found in the combined hours (column C) column. "E4" should be "C4" minus "E3". "E5" should be "C5" minus sum(E3:E4).

The process should be repeated for Project ID 2 starting in "E6".

I would like to point out, that system IDs are generated on a +1 basis, so you would never see a system ID that is smaller than its predecessor (within the same project ID) 

I look forward to your responds- thank you in advance for the support.

[cmcclellan · Accepted solution]

I'm pretty sure this is what you're after :) 

[MarshallG · Accepted solution]

They key to this is using the multi-row formula. First, sort by your project_ID and System_ID (you've already done that for us with your sample data) and then use a multi-row formula to compare the previous row's value for Combined_Hours. Workflow attached.

[felixcaspari]

Thank you! This was very helpful and is working on my actual data set.