Problem with iterative formula - circular reference warning

[aglezb]

[TABLE="width: 620"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]I need help on this problem which (unintentionally) involves a circular reference. Major issues: "DIV/0" and "#N/A" (of course due to the circular).
I'll prefer to eliminate the circular ref. but at the same time would prefer it to be solved by iterative formulas (if such exists) as much as possible before code routine.
In case a circular reference is allowed, I estimate max. of 5 iterations @ 1 as the max. change.
If code is the only solution then it is fine as well.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 473"]
<tbody>[TR]
[TD="class: xl70, width: 473"]B1: FIX VALUE, INTEGER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl70, width: 147"]100 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 473"]
<tbody>[TR]
[TD="class: xl70, width: 473"]B2: FIX VALUE, DECIMAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl70, width: 147"] 37.30 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 473"]
<tbody>[TR]
[TD="class: xl70, width: 473"] B3=2*INT(B2). SHALL INCREASE UNTIL B4<=1.25 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl70, width: 147"]74 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 473"]
<tbody>[TR]
[TD="class: xl70, width: 473"]B4=B1/B3.
- IF B4>1.25, THEN VALUE B3 HAS TO BE INCREASED IN 1 UNIT UNTIL THIS RATIO (THIS CELL B4) REACHES A VALUE <=1.25.
- INCREASED VALUE OF B3 SHALL BE PLACED IN CELL C3.
i.e. C3 = B3+1 ITERATIVELY[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 473"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl70, width: 147"]1.35[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Thank you in advance.
AG

 

[candybg]

Why not use SOLVER?

 

[Eric W]

I see 2 ways to do this without code.

First, make sure you have iterative calculation enabled. (File > Options > Formulas > Calculation Options > Check the box). Set B1 to 100, B2 to 37.3, B3 to 74, and B4 to =B1/B3. Then change B3 to =IF(B4>1.25,B3+1,B3).

Second, fill the values as before, leaving B3 = 74. Then go to the Data tab > What-If Analysis > Goal Seek > Set cell: B4 > To Value: 1.25 > By changing cell: B3 > Click OK.

For more complex problems you can use Solver.

Hope this helps.

 

[aglezb]

why not use solver?

what is solver?

 

[aglezb]

Eric.
Thank you. I think option 1 will give me more flexibility if I tweak your suggestion a bit.
Now, what is Solver??

 

[J.Ty.]

Your problem does not require Excel and VBA, but a little bit of elementary school mathematics.

1) Your stop condition is 100/B3<=1.25
2) Using school math this is equivalent to B3>=80.
3) INT(B2)=37
4) So your initial B3=2*37=74
5) Therefore after the sixth increase of B3 it will attain the value 80 which will stop the iteration.

J.Ty.

 

[Eric W]

J.Ty. is correct that in this case, you can solve the equation with a little algebra. I assumed that this was just an example, and you're trying to figure out a process and not a specific problem.

The Solver is a built-in tool that can solve systems of equations. It's not installed by default, but you just have to check a box to install it. (File > Options > Add-ins > Select Excel Add-ins from the drop down box on the bottom > check Solver Add-in > OK.) It will show up on the far right of the Data tab.

Depending on what you are trying to figure out, it can be used to optimize a model. It's pretty robust, and has more options than I can explain here. You can Google Excel Solver, or check out links like

https://support.office.com/en-us/ar...g-Solver-9ed03c9f-7caf-4d99-bb6d-078f96d1652c

or even search on this forum for examples of how it's used. It has far more capabilities than iterative calculation or Goal Seek.

Hope this helps.