excelexcel-formula

Circular Reference in Dynamic Array Formula


My actual use case is somewhat more complicated, but I believe if I can solve this minimal example then I should be able to leverage the result.

Say you have a spreadsheet that contains a simple formula.

e.g.

B1=A1 + 16

I can use autofill, but ideally it would be nice to use a dynamic array formula instead.

B1=A:.A + 16

This works, unless any value in column A references column B.

A2=B1

Even though B1 only needs A1 to return its own value, it faults presumably because B1 is technically returning an array and looking at the entire column of A. Is there a clever workaround for these situations or is this simply not a use case for an array formula?


Solution

  • The way to resolve this is:

    A2=A1+16
    

    So, just rethink your solution because the approach you try to apply is not good using any instrumentation not particular Excel.

    P. S.

    The workaround is to enable iterative calculation in Options-->Formulas-->Calculation options.

    enter image description here

    [A2]=B1:B6
    [B2]=A2:A7+16