Kardi Teknomo
Kardi Teknomo Kardi Teknomo Kardi Teknomo


Visit Tutorials below:
Adaptive Learning from Histogram
Adjacency matrix
Analytic Hierarchy Process (AHP)
ArcGIS tutorial
Arithmetic Mean
Bayes Theorem
Bootstrap Sampling
Bray Curtis Distance
Break Even Point
Chebyshev Distance
City Block Distance
Conditional Probability
Continued Fraction
Data Analysis from Questionnaire
Data Revival from Statistics
Decimal to Rational
Decision tree
Difference equations
Digital Root
Discriminant analysis
Eigen Value using Excel
Euclidean Distance
Euler Integration
Euler Number
Excel Iteration
Excel Macro
Excel Tutorial
Factorial Function
Feasibility Study
Financial Analysis
Generalized Inverse
Generalized Mean
Geometric Mean
Ginger Bread Man and Chaos
Graph Theory
Growth Model
Hamming Distance
Harmonic Mean
Hierarchical Clustering
Independent Events
Incident matrix
Jaccard Coefficient
Kernel basis function
Kernel Regression
k-Means clustering
K Nearest Neighbor
LAN Connections Switch
Learning from data
Lehmer Mean
Linear Algebra
Logarithm Rules
Mahalanobis Distance
Market Basket Analysis
Mean Absolute Deviation
Mean and Average
Mean, median, mode
Minkowski Distance
Minkowski Mean
Monte Carlo Simulation
Multi Agent System
Multicriteria decision making
Mutivariate Distance
Newton Raphson
Non-Linear Transformation
Normalization Index
Normalized Rank
Ordinary Differential Equation
Page Rank
Power rules
Prime Factor
Prime Number
Q Learning
Quadratic Function
Queueing Theory
Rank Reversal
Recursive Statistics
Regression Model
Reinforcement Learning
Root of Polynomial
Scenario Analysis
Sierpinski gasket
Sieve of Erastosthenes
Similarity and Distance
Solving System Equation
Standard deviation
String Distance
Summation Tricks
Support Vector Machines
System dynamic
Time Average
Tower of Hanoi
Vedic Square
Visual Basic (VB) tutorial
What If Analysis

How to Use MS Excel Iteration

(Simple Counter )

by Kardi Teknomo

Share this: Google+


In this tutorial, you will learn step by step how to use MS Excel iteration with a working example to make simple counter. Excel iteration is a great tool to perform many interactive simple programs in MS Excel without VBA macro. See how it works in this tutorial.

Why use iteration instead of VBA macro?
How to use iteration in MS Excel?
Working Example of MS Excel iteration

Click here to download the spreadsheet companion of this tutorial.


Why use iteration instead of VBA macro?

Some MS Excel users annoyed by the warning of the existing macro like this:

They may disable the macro but the program will not work. They are never sure whether the worksheet contains viruses or not. This is quite big dilemma for people who do not like to take risk.

Is there any way to make a program in Excel with iteration (similar to While Loop or For Next) without Macro?

Yes, MS Excel iteration can do great job of For-Next Loop for you with no programming at all (actually you may need programming in spreadsheet, but without the macro). Using Microsoft Excel, you can iterate without VBA macro. Thus, your users do not need to worry about the annoying warning about viruses. This MS Excel iteration is one of great tool if you know how to use circular reference for your benefit


How to use iteration in MS Excel?

The key idea on how to use MS excel iteration is to make a circular link in the spreadsheet to accumulate the value and check the Iteration option in menu Tools-Options-Calculation Tab – Iteration check box.

Microsoft excel halt the calculation after Maximum iteration (default is 1000) or after all values in the formulas change by less than Maximum Change (default is 0.0001) between iterations. The loop is automatically bounded either by maximum iterations OR by maximum change between two consecutive iterations. To run the iteration again, you need to press F9.

If you put the Calculation option as Automatic , all computation will be done as usual. If you use Calculation option as Manual, you need to press F9 every step to make the computation change.


Working Example of MS Excel iteration

As an example of MS iteration, let us build a simple counter program. Here is the procedure step by step

1. Type 0 in B3, and 100 in B4. Type “Start” in A3, “End” in A4 and “Counter” in A5.

2. In B5, type =IF(B5<B4, B5+1, B4). Its mean if the counter below 100, Ignore the warning of circular reference by click OK to message below.

3. Click menu Tools > Options … > Calculation Tab. Set the calculation option to Manual , check the Iteration check box and set the Maximum iteration to 1 (see figure below)


4. The simple counter starts with 1. Press F9 function key to increase the counter one at a time until 100

The iteration works very well and the counter can do the job manually. Every time user needs to increase the counter, he or she needs to press F9. To press F9 100 times manually, however, is not a good job for our users. Can we automate the iteration? Yes, you can automate the iteration by setting Calculation option to Automatic and maximum iteration to 100 or 1000 .

This counter, however, still have one problem. If you want to change the initial value of the iteration, you need to type the formula again every time you want to compute. Otherwise, it will use the computed value as the initial value. Some initial value may produce unstable result and #NUM as error in computation. It is quite annoying to retype (or paste) manually the formula again to correct the mistake.

How do we restart the counter back to zero and avoid those initial value problems?

1. Continue our counter above, “Delete to Start” in D3. Put special color to the font and cell to distinguish it as a control button

2. Type additional If-Then rule in B5 becomes =IF(D3="",B3, IF(B5<B4,B5+1,B4) )

3. Click menu Tools > Options … > Calculation Tab. Set the calculation option to Automatic, check on the Iteration check box and set the Maximum iteration to 1000 (see figure below)

4. Every time user deletes the content of control cell D3, the counter reset to zero. If user type any letter or number in the control cell D3, the counter automatically run until reach maximum value of 100. (You may change the value of B4 to 500 and see what happen)

Now you have completed the simple counter with control to reset the counter.

Special note for scientific procedures: Unless you are sure, the convergence of the iteration, the results of the some iteration may be misleading. What you get as the result is the value after Maximum iteration. Another weakness of iteration: You cannot graph the value of the iteration process (use ordinary spreadsheet to do this). For example, if there is a cycle you may not see clearly whether the iteration is convergence.

The spreadsheet example can be downloaded here

See also:
Newton Raphson Tutorial, MS Excel Tutorial index, Kardi Teknomo's Tutorial
Rate this tutorial and send your feedback

This tutorial is copyrighted.

Preferable reference for this tutorial is

Teknomo, Kardi. How to use MS Excel Iteration. http:\\people.revoledu.com\kardi\ tutorial\Excel\Iteration.html


© 2006 Kardi Teknomo. All Rights Reserved.
Designed by CNV Media