Multiple formulae per column based on another column value
I currently use the level column to automatically assign a level number based on the indent value of status of the parent rows. Child rows remain blank in this column.
In the % Planned column I use two formulae to calculate where my project is meant to be:
Parent rows use =IFERROR(AVGW(CHILDREN(), CHILDREN(Duration@row)), 0)
Child rows use =IF(TODAY() > Finish@row, 1, IF(TODAY() < Start@row, 0, ((NETWORKDAY(Start@row, TODAY()) / (NETWORKDAY(Start@row, Finish@row))))))
The problem that I am facing is that these formulae need to be manually input at the start of a project or when new rows are added to an existing project. This is fine if I am the person editing as I know what needs to be done, but is causing problems when other users edit a project plan.
My question is as follows:
Is there a way that I can use the level column status to automatically assign the parent or child row formula? e.g. If Level@row is blank, apply the child row formula, or if Level@row is not blank, apply the parent row formula?
I've been messing around with it but cannot make it work. Appreciate any help.
Help Article Resources
Check out the Formula Handbook template!