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

Options
Matt D
Matt D ✭✭
edited 03/02/22 in Formulas and Functions

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!

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Matt D
    Matt D ✭✭
    Options

    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.
    Genevieve P. Employee
    Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Matt D
    Matt D ✭✭
    Options

    Hi @Genevieve P.

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

    Best,

    Matt

  • Genevieve P.
    Options

    Wonderful! I'm glad I could help 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!