W. Gary Martin home page > CTSE 7970 > Lab 2

CTSE 7970, Summer 2001
Lab 2 – Spreadsheets II

1.     Iterative formulas vs. closed formulas. The formulas you have created are all iterative (sometimes called recursive) since they build each successive term from the previous term. We can also build the sequence from a closed formula, so that it only depends on the index number of the term. For example, to find the nth term of an arithmetic sequence,
a_n = a₁ + (n-1)d, where a₁ is the first term and d is the common difference.

a.      Set up a general arithmetic sequence using the closed form.

• Set up constants for a₁ and d.
• In column A, set up n, giving the term numbers.
• In column B, compute the terms, based on the values in column A.

b.     Here’s a shortcut:

• Highlight the values in column A. Define this range of cells as “n” (using “Define… Name” from the Insert Menu.)
• You can now set up the formula in column B in terms of n and fill down.

c.      Now set up a general geometric sequence using a closed formula. Recall that a_n = a₁*r^n-1.

2.     Functions.

a.      Set up a column for x-values, ranging from –5 to 5 and stepping by 0.5 (i.e., -5, -4.5, -4, …) In the column next to it, compute the functional value for a function, say y=x²+3x-5.

• NOTE: Use the trick from #1(b) to make this easier.
• NOTE: Use “^” for exponents.

b.     Use your table to identify where the zeroes of the function will be. How could you get a more exact answer?

c.      To make this easier, you could define constants for the first x-value (perhaps “first”) and the value you add between terms (say “step”.) Now change these values to get a better approximation for the roots.

3.     Graphs.

a.      Now let’s add a graph. Highlight all of your x and y values at the same time. Then under the Insert menu, select Chart. Choose the “xy scatterplot” chart type and hit return repeatedly. The graph will appear! You may want to select “Chart Type” from the Chart menu and experiment with the different kinds of plots.

b.     Change your x-values and watch how the graph changes.

c.      For fun, add a third column to your sheet, in which you compute values for y=2x-3. Where do your two functions intersect?

d.     Now let’s add this function to the graph.

• Click on the graph, and from the Chart menu, select Source Data.
• Change “B” to “C” in the range of cells given, and click OK.

Back to top