How to eliminate circular reference error when two columns' formulas reference each other?

Matt D

I have three columns [PO Cost], [% Accrual Est], [Accrual Est].

I want to automatically populate the [% Accrual Est] and [Accrual Est] columns based on inputs to [PO Cost].

However, the formulas in the two columns that I want to automatically populate reference one another, so I receive CIRCULAR REFERENCE in [% Accrual Est] and BLOCKED in [Accrual Est].

The following formulas are in [% Accrual Est] and [Accrual Est], respectively:

=IF(Parent@row = 0, [Accrual Est]@row / [PO Cost]@row, IF(Parent@row = 1, [% Complete]@row))

=IF(Parent@row = 0, SUM(CHILDREN()), IF(Parent@row = 1, [PO Cost]@row * [% Accrual Est]@row))

[% Accrual Est] is suppose to return the percentage of PO Cost accrued.

[Accrual Est] is suppose to return the dollar amount of PO cost accrued.

I am able to break the formulas apart and use the individual parts in individual cells, but when I try to write the formula to combine them for automation, I get errors.

Please let me know if there is a way to bar against the circular reference error. Thanks!

Genevieve P.

Hi @Matt D

No problem! We can add in an OR statement, like you suggested, that says if the SUM of CHILDREN() = 0, then  [PO Cost]@row * [% Complete]@row

We'll also need to swap around the order of your statements to make sure that it first checks the SUM of the Children before checking if the Parent@row = 0.

Try this:

=IF(OR(Parent@row = 1, SUM(CHILDREN()) = 0), [PO Cost]@row * [% Complete]@row, IF(Parent@row = 0, SUM(CHILDREN()))

Cheers,

Genevieve

Genevieve P.

Hi @Matt D

This isn't quite possible to do, as the calculation of each formula depends on the other formula, which creates the circular reference.

The issue is the end part of your second formula:

IF(Parent@row = 1, [PO Cost]@row * [% Accrual Est]@row))

Since we know that if the Parent column = 1, the % Accrual is actually the same as the % Complete from your first formula: IF(Parent@row = 1, [% Complete]@row, this means we can swap out what column you're referencing to get rid of the error.

Try this:

=IF(Parent@row = 0, [Accrual Est]@row / [PO Cost]@row, IF(Parent@row = 1, [% Complete]@row))

=IF(Parent@row = 0, SUM(CHILDREN()), IF(Parent@row = 1, [PO Cost]@row * [% Complete]@row))

Cheers!

Genevieve

Matt D

Hi @Genevieve P.

The edit to my formula that you suggested worked! However, I have a follow-up question.

Some of my parent rows (0) have children rows (1) without PO costs. The PO cost is simply inserted to the parent row, which has additional tasks that roll up underneath. Therefore, when Parent@row = 0, SUM(CHILDREN()) does not work. I need the formula to determine that there is nothing to SUM from the children rows and use [PO Cost]@row * [% Complete]@row.

Is there some way to add to my formula an OR statement so that if there is nothing to SUM from the children rows, the formula instead uses [PO Cost]@row * [% Complete]@row?

Thank you for all your feedback!

Genevieve P.

Hi @Matt D

No problem! We can add in an OR statement, like you suggested, that says if the SUM of CHILDREN() = 0, then  [PO Cost]@row * [% Complete]@row

We'll also need to swap around the order of your statements to make sure that it first checks the SUM of the Children before checking if the Parent@row = 0.

Try this:

=IF(OR(Parent@row = 1, SUM(CHILDREN()) = 0), [PO Cost]@row * [% Complete]@row, IF(Parent@row = 0, SUM(CHILDREN()))

Cheers,

Genevieve

Matt D

Hi @Genevieve P.

The formula worked like a charm - thank you! Stay well and stay safe.

Best,

Matt

Genevieve P.

Wonderful! I'm glad I could help 🙂