Allow Iteration Calculations In Excel

Excel iterative calculation and circular reference

To understand what is Excel iterative calculation, you should first understand what is circular reference.

Circular reference happens when you type a formula in one Cell which refers to another Cell, but that Cell also refers back to the Cell.

In Excel 2016, Excel 2013, and Excel 2010, go to File Options Formulas, and select the Enable iterative calculation check box under the Calculation options. In Excel 2007, click Office button Excel options Formulas Iteration area. In Excel 2003 and earlier, go to Menu Tools Options Calculation tab Iterative Calculation. Sep 25, 2019 To enable and control iterative calculation in Excel Office 365 in windows version as I mention above please, open Excel file click File Options Formulas, and select the Enable iterative calculationcheck box under the Calculation options. You can follow my below screenshot steps. Let us know if you need additional assistance.

For example, Unblocked school downloadshome.

Though it is usually preferable to avoid circular references, they will appear occasionally when building complex calculations. One common example is in tax.

Cell A1: = 1+A2
Cell A2: = 1+A1

The above formula refer to each other, this is called circular reference (refer to each other forming a circle). The formula will fail and the below error box will prompt.

Excel Iterative calculation

Since Excel 2007, a new function was introduced to allow circular reference.

Below is a screenshot of Excel 2013, other versions also have the check box 'Enable iterative calculation', check the box to enable.

'Maximum Iterations' define how many times you want to loop the calculation.

'Maximum Change' define the maximum amount of change you will accept between recalculation results, the smaller the slower but more accurate.

To illustrate how iterative calculation works, follow the below steps.

1) Disable 'iterative calculation'

2) Type the following formula

Cell A1: = 1+A2
Cell A2: = 1+A1

3) Set Maximum Iterations to 10, leave Maximum Change default, check the box to enable iterative calculation

Result

A1 will immediately show 19 and A2 will show 20. The underlying mechanism for each iteration is shown below.

Iteration	A1 value	A2 value
1	1	2
2	3	4
3	5	6
4	7	8
5	9	10
6	11	12
7	13	14
8	15	16
9	17	18
10	19	20

Note carefully that Workbook Calculation is set to 'Automatic' by default.

Every time you edit any Cell (Worksheet_Change Event is triggered), Cell A1 and A2 will recalculate one more time based on the previous result.

That means A1 becomes 39 and A2 becomes 40 for the first recalculation.

To avoid recalculation, you should change the Workbook Calculation from 'Automatic' to 'Manual'. A1 and A2 will only refresh if you edit them.

Purpose of Excel iterative calculation

The above example demonstrates the mechanism behind the iteration calculation process, but why do we need it?

The purpose of it is to simulate the VBA Loop, but you don't need to know how to write VBA.

Take For Loop as an example, the below code simulate the actions above.

Public Sub floop()
For i = 1 To 10
Range('A1').Value = Range('B1').Value + 1
Range('B1').Value = Range('A1').Value + 1
Next i
End Sub

You can even insert IF condition in the iterative calculation to tell when to stop, exactly like VBA Loop.

Cell A1: = 1+A2
Cell A2: =IF(A1+1>10,10,A1+1)

Outbound References

The Colebrook-White describes the relationship between the Reynolds Number Re, the pipe relative roughness e, and the friction factor f in turbulent pipe flow.
Usually, we fix a value for Re and e, and compute f. However, this equation cannot be solved explicitly, so numerical iteration is needed.
There's several ways we can do this in Excel. I'll outline the two most convenient methods. If you just want the spreadsheet, scroll to the bottom of this post and download the spreadsheet.

Spreadsheet Setup
This is a screengrab of the spreadsheet included with this post.

Note that the parameters to be entered by the user are highlighted green. I've also assigned cell names to each parameter, so the pipe roughness is e, the pipe diameter is D, and so on.
Method 1 - Using VBA and Goal Seek
This VBA macro fires up Goal Seek whenever there is any change in the worksheet (due to the Worksheet_Change() event).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bSuccess As Boolean
On Error Resume Next
bSuccess = Range('fCheck').GoalSeek(0, Range('f'))
On Error GoTo 0
If Not bSuccess Then
MsgBox 'Goal Seek Failed for Cell 'X'!'
End If
End Sub
The code finds the value of f that will make fCheck equal 0.
Method 2 - Worksheet Iteration
You'll need to enable worksheet iteration so that Excel can correctly resolve a circular reference. In Excel 2010 go to File>Options>Formulas, and check Enable Iterative Calculations. You might also want to increase the number of iterations to 500, and reduce the maximum change to 0.0001).
The trick here is to rearrange the Colebrook-White equation so you have nothing but the friction factor f on the left hand side of the equation (it can appear on the right-hand side together with the other terms in the equation).
This is the formula I've typed into Cell B17 of the spreadsheet - it's just the rearragned Colebrook-White Equation, but with one minor change.

How To Enable Iterative Calculation In Excel

Allow Iterative Calculations Excel

Calculations

When Excel starts iterating, it initializes B17 with a value of zero. However, this will a divide-by-zero error. To resolve this, I've added a very small number (1E-300) to B17. This doesn't significantly change the accuracy of the computer friction factor.
Both Method 1 and 2 give roughly the same value of f. I prefer Method 1 (purely because enabling spreadsheet iteration means you will not necessarily be informed of any unintended circular references).