Search code examples
exceliterationms-office

Excel: Cell displays different value than it is linked to (iterative calculation)

I'm building an Excel model using iterative calculations. The problem results in the yellow cells shown below:

One cell is linked to another and displays a different value than it should. The bottom value (29.4%) is the correct one, so of course I would like Excel to actually use that one. When clicking F9 (third picture) one can see that the cell value should technically be correct.

Please let me know if you need any further information as I'm new to SO. Thanks in advance!

enter image description here enter image description here enter image description here

Solution

  • Using circular references in Excel requires planning of the layout of the model, in order to get a stable result. And the calculation order is not the same with iterative calculations. One consequence is that if the cell referring to the "iterative cell" is above, it may show the result of the previous iteration.

    Here is a very simple model demonstrating that:

    A14:  1
A15:  =A15 + A14
B13:  =A15
B17:  =A15

    The following shows the results of four single iterations, one at a time. You will see that B13 is displaying the results of the previous iteration; whereas B17 displays the result of the last iteration

    enter image description here

    If I recall correctly, with iterations, calculations proceed by worksheet in alphabetical order (not sure if that refers to codename or sheetname), and on a worksheet, from left to right and top to bottom.