Examples and concepts of Excel iterative operations

Source: Internet
Author: User

Excel in the iterative operation, can solve many problems, at the same time, iterative operation also to many novice learning trouble.

Now, this site gives you a detailed description of what is excel in the iterative operation, how to use the iterative operation.

First, the concept of iterative operations

The so-called iterative operation, there is nothing strange, please do not believe that the so-called brick house in some "authoritative" books in the view of the stated.

Simply put, an iterative operation is a recursive computation, or it can be simply called a cyclic operation.

Anyone who has studied computer programming has no knowledge of recursive operations.

If you have not learned recursion, then this site to introduce you to a simple example.

For example, to solve the equation f (x) = 0, the Newton tangent method constructs the iterative format as follows: X (n) =x (n-1)-f[x (n-1)]/f ' [X (n-1)] (n=1,2,3,......), taking any value x (1) in the range of consideration, can be computed with the above iteration formula X (2), X ( 3), ... in theory, when n→∞, we get the root of the equation, but in fact we can't always make the iteration count to infinity, we need to stop the iteration at some point, there are usually two ways to control the number of iterations:

1, select the number of iterations N, when the number of iterations reached N, stop the iteration;

2, the choice of precision ε, when | When X (n)-X (n-1) |<ε, stops the iteration.

The default number of iterations in Excel is n=100, the precision is ε=0.001, the tool → option can set itself, so that the results of the approximate degree of better.

Second, the use of iterative operations

For example, you enter a value of 10 in cell A1, and then select cell B2 to perform menu actions: Tools → options, and the following dialog box pops up.

Tick check "iterative calculation", and then enter the number of times, such as this example of 1, you can also set the maximum error.

OK, later, confirm and exit the dialog box.

Next, select B1 cell and enter the formula: =A1+B1

You might ask, why is the B1 cell's formula: =A1+B1, the cell itself calls the value of the cell itself, which is contrary to common sense, in general, will pop up the formula error prompts. Since we have already set up the iterative operation on the above, the prompts are not popped.

You may understand that the cell itself calls its own data and then the result is presented to the cell itself. This is the iterative operation, but also the cyclic operation.

The following content will give the custom function code for the iterative operation.

Common problems in iterative operations

Problem: The update of any of the grid data in the entire table will cause B1 iterations to be updated, which results in changes in B1 data.

Reason: Excel's functions and formulas are global, regardless of which grid data updates, the current table all the squares of data will be recalculated and updated.

Workaround: Change the B1 unconditional update to restricted. Select B1, enter "=if (cell (" address "=" \$a \$, a1+b1,b1) ". Select B1 and drag down to extend the iteration calculation to the other rows in the same column. Parameters 2 and 3 of the IF function can be updated automatically, and "\$a \$" in Parameter 1 is not automatically updated because it is a string, so you need to change it manually, such as the 2nd Behavior =if (cell ("address") = "\$a \$ ", a2+b2,b2), change \$a\$1 to \$a\$2, and so on.

Attention:

1. According to the restriction condition of function, after entering data in A1 square, press ENTER key or TAB key can update B1, but use other squares of mouse point, B1 cannot iterate update.

2. Do not click A1 when one of the squares has input focus, as this will cause the B1 to iterate over the update.

Four, the custom function of the iterative operation

The following function code is one of the function codes for the iterative operation.

Function MyDD (i as single)

i = I/5

If I < 5 Then

MyDD = i

Else

MyDD = MyDD (i)

End If

End Function

People who know how to program design understand that the above iterative operation function code, in fact, is recursive code, in other words, also loop code.

Related Keywords:
Related Article E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth \$300-1200 USD