This article discusses reasons for the ‘Circular Reference’ error that you may come across in the Project Planner (Advanced) Excel Template, and how to avoid them. ‘Circular Reference’ is the concept where one formula depends on its own result as its input.
VIDEO DEMO
The Project Planner Excel template has automatic scheduling feature which will schedule each task (at a daily level) across all projects and resources. Though it schedules automatically, it allows the user to control the schedule through multiple ways. It allows inputs from the user on 1) Project priority 2) Task Priority, 3) Predecessors, 4) Project Preferred Start Date, 5) Task Preferred Start Date and 6) Max Daily Allocation %. The scheduling engine (Formulas) takes these inputs and builds the schedule for each task , one by one. It starts with the most important task and then goes down the order of priority.
The fundamental concept is that the most important task should be given the most chance to complete as soon as possible (considering other restrictions, of course). The following describes how the prioritization is done.
- In order to determine the priority of tasks, a calculation [(‘Project Priority *10000)+ Task Priority’] is used. The lower the value is, higher the priority.
- If Project Priority is left blank, the project will not be included in planning at all.
- If two projects are given equal Project Priority value, the tasks are ordered based on Task Priority
- If Task Priority is not given it is assigned 9999 by default.
- If two tasks have the same Task Priority, then the order in which they appear in the TASKS table is used to prioritize.
An error/warning message that you might come across while using the Project Planner (Advanced) Excel template is the ‘Circular Reference’.
Even after you click OK, the error will remain. You will see a message in your Excel status bar at the bottom left.
When this happens, it is important that this is resolved. Otherwise, the calculations will be incorrect. Now, let’s see why this error appears and how to avoid it.
SCENARIO 1: Task is dependent on itself
If you assign a task as its own predecessor, then it creates circular reference. A task cannot begin until the predecessor task is complete. As shown in example above, Task 1 cannot begin until Task 1 is complete. That is a tough one, even for Excel. ? Please ensure that this does not happen.
SCENARIO 2: Task’s predecessor is dependent on it
In the example above, Task 1’s predecessor is Task 2 while Task 2’s predecessor is Task 1. The result is a circular reference error. Please check if this is true with your input data if you come across the error.
SCENARIO 3: Task Priority Conflicts with Dependency
In this scenario, Task 1 and Task 2 are both assigned to same resource. Task 1 is Task 2’s predecessor, but the Task priority is set such that Task 2 is more important. This creates a circular reference in scheduling logic. Please swap the Task Priority values in this case to avoid the Circular reference.
The next one is similar to the above scenario.
When Task priority is not entered it is assumed to be 9999 by default. In this case, both tasks will have 9999 and hence the order in which they are entered becomes the critical factor. Since Task 1 is entered first, it becomes higher priority. But Task 1 has Task 2 as predecessor and that creates the Circular Reference. Please enter Task Priority values (Task 1 = 2, Task 2 = 1) and that will resolve the circular reference.
I have used single project in the examples above, but the concepts apply to tasks across multiple projects as well.