Assistance with Conditional Progress Formula Referencing Only One Column

Gaia Amaia

I’m trying to create a formula that dynamically calculates progress based on the value in a Status column. In some cases, I want the formula to preserve the existing progress value when the status changes to “Pending” — essentially freezing the value that was previously calculated.

However, since I only have one column (Status) to base the logic on, and no secondary reference column (like a unique ID or milestone type), I wasn’t able to build a reliable formula that remembers the previous progress state. I tried to replicate logic from another sheet where I had an additional supporting column, but it didn’t translate well here.

Is there a way to implement a logic that preserves a calculated value once a row reaches a certain status (e.g., “Pending”), even if the conditions change afterward — without needing a helper column?

Any guidance or workaround would be greatly appreciated.

I have used this one and it worked - on another table

=IF(Status@row = "Done", 1, IF(AND(CONTAINS("Delivered", Milestone@row), OR(Status@row = "In Progress", Status@row = "Pending")), 0.8, IF(AND(CONTAINS("Test", Milestone@row), Status@row = "In Progress"), 0.55, IF(AND(CONTAINS("Test", Milestone@row), Status@row = "Pending"), 0.55, IF(AND(CONTAINS("Install", Milestone@row), Status@row = "In Progress"), 0.2, IF(AND(CONTAINS("Install", Milestone@row), Status@row = "Pending"), 0.2, IF(AND(CONTAINS("Test", Milestone@row), Status@row = "Planned"), 0.4, IF(AND(CONTAINS("Install", Milestone@row), Status@row = "Planned"), 0.1, 0))))))))

This the table I need the formula to work on -

(For now I have added pending as a checkbox but this is not working for me.)

Thanks,
Gaia

Paul Newcome

You could use a helper column and a chance cell automation to flag rows that have changed to the specific status and then incorporate that.