Function help for using iteration and circular reference

[AshbyB]

I have a problem that I believe requires a circular reference. I would like to stop the iteration by copying a value only to a cell thus replacing the formula with the value. Is there a function that can help me do this?

The algorithm is:

Calculate the values of the cells X3, Y3, and Z3. 

1. Find the row with the first zero in Column P.

2. Replace the formula in X3 with the value in Column A of the row found.

3. Calculate the spreadsheet which will change the values in Column X and Column P.

4. Repeat for Y3 and Z3. Iteration will stop when the formula in Z3 has been replaced by a value.

Any ideas on how to do this with a formula and function?

[#2]

Hi @AshbyB 

In my opinion, you might need to make use of macros.

[#3 · Accepted solution]

@AshbyB 

Yes, you can do that also with formulas (if you don't want to use macros), i.e., you can make a formula in a cell reference itself (the same cell where the formula resides) and check its value against some starting default of your choosing, for instance check if it's zero and if it is, then return some other value, but if it's not zero, return the existing value - this way the initial value gets calculated only once and never changes thereafter.

That's circular reference. To enable that, you go: Excel>Options>Formulas>Enable iterative calculation:

in X3=IF(X3=0,YourLookedUpValFromCol_AWherCOlPEquals0,X3) << so, this checks if the value in X3 is zero, if it is - return the looked up value, if it is not zero - return current value

in Y3=IF(AND(Y3=0,X3<>0),YourLookedUpValFromCol_AWherCOlPEquals0,Y3) << so, this again check the current value in the Y3 itself but it also checks the X3 too, i.e., this will return (and retain) the looked up value if the X3 had already done that

in Z3=IF(AND(Z3=0,Y3<>0),YourLookedUpValFrom_A_wherPeq0,Z3) << similar to the Y3 above.

[#4]

Thank you for your response. I will try the macro approach if a circular formula does not work.