Lesson 8

Spreadsheet Shortcuts

Let's explore recursive formulas in spreadsheets.

8.1: Tables of Equivalent Ratios

Here is a table of equivalent ratios:

\(a\) \(b\)
3 15
10 50
6 30
1  
  80
  1. Complete the table with the missing values.
  2. Explain what it means to say that the pairs of numbers are equivalent ratios.

8.2: The Birthday Trick

Use the applet to answer the questions.

 

  1. In cell B4, we want to enter = B1 * 5 to multiply the month by 5. Enter this, but when you are about to type B1, instead, click on cell B1. This shortcut can be used any time: click on a cell instead of typing its address.
  2. Practice this technique as you program each cell in B5 through B10 to perform the right computation.
  3. When you are finished, does cell B10 show a number that contains the month and day of your birthday? If not, troubleshoot your computations.
  4. Try changing the month and day in cells B1 and B2. The rest of the computations should automatically update. If not, troubleshoot your computations.

 



Why does this trick work? Try using \(m\) for the month and \(d\) for the day, and writing the entire computation as an algebraic expression. Can you see why the resulting number contains the month and day?

8.3: Using Spreadsheet Patterns

The spreadsheet applet contains a table of equivalent ratios.

  1. Use spreadsheet calculations to continue the patterns in columns A and B, down to row 5. Pause for discussion.
  2. Click on cell A5. See the tiny blue square in the bottom right corner of the cell? Click it and drag it down for several cells and let go.
  3. Repeat this, starting with cell B5.

Summary

Sometimes you want to create a list of numbers based on a rule. For example, let's say that the cost of a gym membership is a \$25 sign-up fee followed by monthly dues of \$35. We may want to know how much the membership will cost over the course of 6 months. We could use a spreadsheet and set it up this way:

Spreadsheet with rows 1 to 7 and columns A and B. Rows in column A contain sign-up fee, total cost after 1 month, total cost after 2 months, and so on until 6 months. B1 contains 25. B2 contains =B1+35.

Which results in:

Spreadsheet with rows 1 to 7 and columns A and B. Rows in column A contain sign-up fee, total cost after 1 month, total cost after 2 months, and so on until 6 months. B1 contains 25. B2 contains 60.

See the little square on the lower-right corner of cell B2? If we click and drag that down, it will keep adding 35 to the value above to find the value in the next row. Drag it down far enough, and we can see the total cost after 6 months.

Spreadsheet with rows 1 to 7 and columns A and B. Rows in A contain sign-up fee, total cost after 1 month, total cost after 2 months, and so on until 6 months. Rows in B contain 25, 60, 95, 130, 165, 200, 235.

Any time you need to repeat a mathematical operation several times, continuing a pattern by dragging in a spreadsheet might be a good choice.