INDZARA

Circular References in Project Planner Excel Template

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.

An error/warning message that you might come across while using the Project Planner (Advanced) Excel template is the ‘Circular Reference’.

Excel - Circular Reference Error - Project Planning
Excel – Circular Reference Error – Project Planning

Even after you click OK, the error will remain. You will see a message in your Excel status bar at the bottom left.

Excel – Circular Reference Message

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

Excel – Circular Refecence – Scenario 1

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

Excel Circular Reference – Scenario 2

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

Excel Circular Reference – Scenario 3a

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.

Excel Circular Reference – Scenario 3b

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.  If there are any questions, please leave a comment below.

Exit mobile version