Recursive and Explicit Equations in Excel

Math 130

Fall 04

Borkovitz

Recursive and Explicit Equations in Excel

This handout describes two different ways to make a list of even natural numbers (2, 4, 6, 8, …) using Excel: first using a recursive equation and then using an explicit equation. The techniques used in these simple examples are applicable to many other problems.

Recursive Equation:

A recursive equation can be thought of as analogous to a flight of stairs. In order climb a flight of stairs, we need to be able to do the following:

1. Get to the first step (or sometimes the first few steps). In terms of listing the even natural numbers, we need to know that we start with 2. Mathematically, this is called the initial condition (for more complicated recursive equations, we sometimes need several initial conditions).

2. Get to the next step from the previous step. With listing the even natural numbers, to get the next even number, we add 2 to the previous even number. Mathematically, this is the recursive equation (we can, for example, formally write , but if you find this notation confusing, don’t worry; the notation used in Excel is more intuitive).

To enter the recursive equation in Excel, we do the following:

First, we enter the initial condition:

Then we enter the equation, making reference to the cell(s) containing the initial condition.

Note that Excel reads A1 as “the cell above.” We first hit enter:

Then we return to cell A2, and use the fill handle in the bottom right corner to drag the equation down:

Drag Here

Below we see how Excel automatically changed the formulas in each cell (Use CTRL ` to get this view the ` is left of the 1 on many keyboards):

Note that with a recursive equation, if we change the initial condition, we automatically change all the cells. For example, suppose we change cell A1 to be 1, then we will get the following:

Note that the equations didn’t change, just the starting “step,” and now we have the odds instead of the evens.

2. Explicit Equations

Continuing our staircase metaphor, an explicit equation allows us to jump immediately to a step, without “climbing” the rest of the staircase first. In the case of the evens, we know that the even natural number is just .

Sometimes it can be confusing to enter explicit equations in Excel, because in order to do so, we must first enter the “n’s” or the “input” values in one column (technically, the independent variable). These are the values that tell us what “step” we might want to go to. Then, in another column, we enter our equation that tells us how to take the “input” to get our “output” (technically, the output is the dependent variable).

Let’s look at our example. We start by using a recursive equation to list the first few natural numbers:

Now in column B, we enter our explicit equation; note that Excel reads A2 as “the cell to the left” in this case:

Now we go back to B2 and use the fill handle to drag. Note that we don’t need an initial condition in column B, we just drag starting at the first cell.

Caution: If we want to change the equation in column B, we will have to drag it again, we cannot just change one cell.

We can often use Excel to help find an explicit equation, especially in situations where it’s easier to find the recursive equation. For example, suppose we want to find an explicit equation for the odd numbers. We might notice that they are one less than the entries in Column B, so one possible equation for the odd number is . We can enter this equation and check in against the recursive equation we found earlier for the odds.

To Commentary Doc File PDF File Home