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.
Solved! Go to 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.
Thank you! This was very helpful and is working on my actual data set.