When do I need Iterative Calculations? At Top Shelf Models (TSM), we use iterative calculations everyday to calculate construction interest. When iterative calculations are enabled the Excel file stops calculating after a determined amount of iterations and the circular formula has found a stable result. If there is a circular reference in your file, the formula is stuck in a loop calculating repeatedly, attempting to find a solution. This means that a formula directly or indirectly refers to its own cell. In most institutional-quality financial models in Excel, there are calculations that need to be circular. By analyzing the previous results, Excel can find the likelihood of possible solutions. Iterative calculations help Excel find the solution to formulas by performing the same calculation repeatedly using previous results. What are Iterative Calculations? Iterative calculations are repeated calculations until a specific numeric condition is met. More than likely, this is due to iterative calculations not being enabled. You often see the word “Calculate” at the bottom left of the screen or the dreaded Circular Reference Error pop up box may appear. While the above can be done without a LET, using LET improves readability of the formula and computes twice as fast as without LET.Ĭopy the following table to cell A1 of a blank worksheet if you'd like to walk through this example yourself.Have you ever noticed that your Excel formulas are not updating or you see the Excel recalculate notice? Frequently when working in a complicated Excel file, you will notice that the calculations do not seem to be updating when changing an assumption. Suppose you have some raw sales data, and you'd like to filter that data to show one person, and add a dash to any blank cells. When this formula is input into a cell, it will return the value 6. "a" is valid but "c" is not because it conflicts with R1C1 style references.Ĭonsider the simple expression "SUM(x, 1)" where x is a named variable that can be assigned a value (in this case, x is assigned the value 5). Names of variables align with valid names that can be used in the name manager. The last argument must be a calculation which returns a result. With the ability to declare and name variables, you can give meaningful context to yourself and consumers of your formula. LET allows you to call the expression by name and for Excel to calculate it once.Įasy Reading and Composition No more having to remember what a specific range/cell reference referred to, what your calculation was doing or copy/pasting the same expression. Improved Performance If you write the same expression multiple times in a formula, Excel calculated that result multiple times. You must define at least one name/value pair (a variable), and LET supports up to 126. To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax. These names only apply within the scope of the LET function. This allows storing intermediate calculations, values, or defining names inside a formula. The LET function assigns names to calculation results.