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.

Best Answer

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓

    Hi @John Kelly

    This should do the trick

    =IF(ISBLANK(Level@row),IF(TODAY() > Finish@row, 1, IF(TODAY() < Start@row, 0, ((NETWORKDAY(Start@row, TODAY()) / (NETWORKDAY(Start@row, Finish@row)))))),IFERROR(AVGW(CHILDREN(), CHILDREN(Duration@row)), 0)

    Hope that helps




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!