Hi Tom,

Thanks for sharing a file with your before and after results. I wish everyone would do this! That said, I think there may be some errors in the Remaining Contingency column, assuming I understood the requirement correctly (it's possible I didn't  because I didn't understand what you meant by "For planned changes, I want the running total to suspect").  Cells O29 and O37 appear to have the wrong values.

Based on my understanding, it seems to me that you need two separate running totals, one that excludes the Planned values and one that includes them. I would therefore add a column that only includes the Executed Values and calculate the running total for that column. You can also create a running total for all values, and use that for the Remaining Contingency calculation.

See example file attached - query Schedule_of_Project_Contingencies8.

If I've misunderstood, it would be helpful if you included formulas in the columns you want calculated so I can see how you're coming up with the numbers.

Mynda

Hi Mynda

Thank you for looking at this. I did a typo when I said suspect, it should have been suspend to read "For planned changes, I want the running total to suspend". I have re-written desired results to show this.

Does this make things clearer?

Tom

Hi tom,

Thanks for clarifying. I think the best you can do is take the file I already uploaded and add another column for "Remaining contingency for Planned Items Only" with this formula:

= if [Status] = "Planned" then [Contingency]-[RT] else 0

And replace the 'Inserted Subtraction' step with another custom column for "Remaining contingency for Executed Items Only" with this formula:

= if [Status] = "Executed" then [Contingency]-[Executed Values RT] else 0

Hope that helps.

Mynda