Multiple formulae per column based on another column value

John Kelly

Hi,

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.

Paul McGuinness

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

Thanks

Paul

Paul McGuinness

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

Thanks

Paul

John Kelly

Hi Paul,

Talk about not seeing the wood for the trees!!! That worked perfect, thanks for the help. I was overthinking it altogether.