Circular Logic Difficulties

Screenshot 2025-03-04 160654.png
Screenshot 2025-03-04 161356.png
Copy1.xlsx

Hello! I am using a small circular formula in Excel. I have set the cycle limit to 100 attempts, and I need this circular logic for this particular calculation to optimize it.
It works sometimes, but sometimes it gets stuck like this where call F26 thinks there is an error in cell G28. However, G28 does not have an error in it. Pressing F9 for a general update doesn't resolve this issue.

If I specifically update cell G28, then both G28 and F26 show an error. Then I can update cell F26 again and get back to the same stuck state. Why does this happen? Is there anything I can do to fix this?

Here is what I want to happen: Both F26 and G28 should start at their default values. Then, since G28 has a value in it, then F26 will update with the formula 1.1/G28. Then G28 will check F26 and if it is greater than one of the values, it will update. If it updates to a new number, then G26 will re-calculate and F26 will update again until both of them are happy.
Is there a better way to do this?

Thanks!

Probably not much we can do to debug a picture. Can you isolate the calculation and put it in a workbook to upload to the forum? We may have better luck helping you debug the problem if we can see it in the spreadsheet.

Trying my best from the picture, can you verify that I am understanding the logic correctly?

G28 -- If I follow correctly, G28 is a simple IFS() function that will return 1 of 4 possible values (assuming F26 is a number and not an error).

F26 -- Again, if I follow correctly, The IFS() function in F26 will always return 2.5*22.2, unless G28 is exactly 0. As currently programmed, G28 will not return 0. There is no real circular logic. On the off chance Excel sees 0 in G28, F26 will return an error, which will propagate to G28. Excel may choose to terminate the calculation at this point. Even so, once you get F26 to calculate again, it should revert back to 55.5.

I can't be sure, but something in your circular logic seems unexpected to me. As currently programmed, the only time the circular logic is in play is during the error case when G28=0.

I could easily be missing something, as I noted earlier, pictures are notoriously difficult to debug. If I understand correctly, there is not much circularity to the logic, except in the error case, and the programming is such that forcing a couple of recalculations of these cells should break the circular logic. If I set up this logic in my copy of Excel, I cannot replicate the situation where these cells do not respond to F9. It feels to me like we need more information to recreate the problem.

You have a circular reference: D6 needs E8 and vice-versa. You need to resolve this.

You can't have that circular reference - that's my point.

Why does this happen?

You can't have two cells with formulae that each rely on the other to get a result.

Is there anything I can do to fix this?

Yes - change your approach. I don't know if SOLVER might be a possibility in this case. Either that or possibly VBA. If you want the latter, I can move the thread for you.

neither cell should be an error case since I have "IFERROR" to take care of that.

I think you need to look more carefully at your "IFERROR()" logic, because I don't think the IFERROR() is truly preventing all possible errors -- in particular it is failing to trap the "what if G28 is 0?" error case.

If it helps understand what is currently happening:

1) Break the circular reference and enter 0 in G28 (after you get F26 fixed up right, then you can reintroduce the formula in G28). F26 should immediately return #Div/0
2) Select F26 and bring up the Evaluate formula tool (https://support.microsoft.com/en-us/...6-a70aa409b8a7 ). Step through F26 while G28 is fixed at 0:
2a) It evaluates G28 as 0.
2b) 0 is not an error, so the IFERROR() function returns 0.
2c) NOT(0) evaluates as TRUE, because, when converted to boolean, 0 is equivalent to FALSE while any non-zero number is equivalent to TRUE. Inside of Excel, NOT(0) becomes NOT(FALSE) becomes TRUE.
2d) After evaluating the other possible IFS() outcome, Excel finds the first TRUE boolean argument and returns the corresponding output. In this case, the first condition is TRUE, so it evaluates 1.1/G28=1.1/0=error.

Then repeat the evaluate formula steps using any non-zero number in G28. Assuming you see the same as me, you will see that, when G28 is any non-zero number, the IFERROR() returns that number, NOT(non-zero number) returns FALSE, which pushes the IFS() function to return the second outcome 2.5*F24, effectively breaking the circular logic.

What I would expect in F26 is something more like =IFERROR(1.1/G28,2.5*F24). This formula will only return the "default" value for F26 when G28 is 0. For any non-zero case, F26 will return the result of the 1.1/G28 calculation and continue iterating until it stabilizes (in my tests, it stabilized on the first full iteration).

From this side of the internet, I can't be sure exactly how you need the logic in F26 to work. As currently programmed, it seems to me that F26 is not doing exactly what you think it should be doing. I recommend considering more carefully what F26 should be doing, and revise the formula in F26.

Stepping back from what is currently programmed and trying to visualize the overall problem, here's what I see:

1) G28 is a step function. The purpose of the iteration is to determine which "rung" of the step function solves "the problem" (I'm not sure I have enough information to understand exactly what problem we are solving).
2) If I start with a "seed" value in F26 (55.5 seems to be the current choice for a seed value), I will get a "rung" that corresponds with that value. When F26 is 55.5, G28 will be 0.0085.
3) Next iteration will have F26 be 1.1/0.0085~129. When F26 is 129, G28 is the same as before, and the iteration terminates.

Further examination suggests that the current description of the problem has no way for the algorithm to change "rungs" within the iteration, and the algorithm seems to reduce down to the non-iterative expression =1.1/IFS(seedvalue<150,....).

Based on this, it seems to me that something is missing from the overall problem statement. As currently programmed, iteration is not needed, and the problem of "not updating" from the OP is solved by replacing the circular logic with simpler linear logic. But, you also seem certain the iteration is necessary for this problem, suggesting that there is something I am not understanding about the problem.

Not much help, I know. Maybe somewhere in all of that discussion will be something you can pick out that helps understand the problem you are having.

I'm glad to have been some help.

I wouldn't say that Excel cannot handle circular logic. Maybe that programming a spreadsheet to handle circular logic is a whole 'nother ball game compared to programming straight through logic. I have a thread here (https://www.excelforum.com/tips-and-...ind-roots.html ) that shows how to use circular references to solve "root finding" problems.

It's up to you how you want to move forward. VBA or other programming language is one option. Sometimes Solver can be helpful for these. I don't understand the full problem well enough to make a recommendation. You as the programmer are at a point of needing to make some decisions, it appears. Good luck with whatever choices you make for this problem.