Actual Percent Complete Column Formula Yielding #Blocked Error

niccilaw

I am new to doing formulas in Smartsheets and I cannot get the formula below to work. I am sure the syntax is wrong but I am not getting a syntax error. Instead, I am getting a "#Blocked" error. Can someone tell me how to fix the formula so it will yield valid results instead of the error? I do not want to average the children if the task name contains "Delay". Any assistance you can provide would be greatly appreciated.

=IF(AND(NOT(CONTAINS("Delay", [Task Name]@row)),COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(Status@row <> "Complete", 0, IF([Actual Start Date]@row <= TODAY(), MIN((TODAY() - [Actual Start Date]@row) / [Actual Duration]@row, 1), 0)))

Jason Albrecht

Hi @niccilaw

A #BLOCKED error is caused by "at least one of the cells referenced by the formula has an error."

You may want to check if any of the cells referenced in the formula contains an error, which will help narrow down the problem?