Resource Count & Effort Calculation
Hi there, I am struggling with two calculations, namely resource count and effort. Any assistance would be really appreciated.
For resources, I am using =COUNTM([Assigned To]@row), as there can be more than one resource per task. The problem is, this formula works at the lowest level of the task hierarchy, but it just shows 0 at the highest level. I need this formula to work in order for the effort formula to work at the highest level.
The formula I am using for effort is:
=IFERROR((Duration@row * [Resources]@row * [Resource Allocation %]@row), " "). This also works at the lowest level, but not at the highest level. It shows 0 at the highet level.
Many thanks,
Nick
Best Answer

Thanks so much Jmyzk! Much appreciated. I would never have thought of that.
It worked with one exception though. Since, my hierarchy has milestones in it (i.e. where there is a 0 in the duration column) the count descendants formula unfortunately counts milestones as a step when it should ignore it. Don't suppose you know a way around this?
Thanks again
Nick
Answers

Hi @Nick.C
If I need to change the formula between a parent row and a child row, I will use a helper column to determine if a row is a parent or child row.😀
Example helper column
[D] : =COUNT(DESCENDANTS())

Thanks so much Jmyzk! Much appreciated. I would never have thought of that.
It worked with one exception though. Since, my hierarchy has milestones in it (i.e. where there is a 0 in the duration column) the count descendants formula unfortunately counts milestones as a step when it should ignore it. Don't suppose you know a way around this?
Thanks again
Nick
Help Article Resources
Categories
Check out the Formula Handbook template!