Finding the difference between % completed at row and the previous submission

Question

% Completed column is updated incrementally, for example one day its 50% and the following is 100%.

What I need is to have a formula that finds the previous submission that matches the project and primary column, and subtract that % from the % completed at row.

I'm using this to figure out how much SQFT is being produced, so using the example above:

Total GSF 1000

Row 1 = 50% = 500 SF

The second row is calculating the SQFT equivalent to 100% and not the difference, which should be 50%.

Paul Newcome · Answer

Where exactly is your total be ing recorded? Are you able to provide a screenshot for context?

Andy Kolden · Answer

Hi @maria_NOMAD,

This might do what you're seeking to accomplish (be sure to replace the column names with whatever matches your sheet):

=IF(COUNTIF([Project]:[Project], [Project]@row) > 1, IFERROR([Progress]@row - MAX(COLLECT([Progress]:[Progress], [Project]:[Project], [Project]@row, [Progress]:[Progress], <[Progress]@row)), ""), "")