Forum Discussion
Function help for using iteration and circular reference
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.
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.