Lesson 5

The purpose of this activity is for students to learn about formulas in spreadsheets through hands-on experience. This exploration prepares students for using a spreadsheet to calculate the cost of recipe ingredients in the next activity.

Explain what spreadsheets are and how they work, including the following features:

• A spreadsheet is a computer program that lets you organize information in a grid of rectangles, called cells, and to do calculations. There are many different spreadsheet programs, but they all work in basically the same way. The rows are labeled with numbers and the columns are labeled with letters.
• Display this image of a sample spreadsheet.
   

  

  

  Ask the students “What information is in cell B3? What does this piece of information tell us about the situation?” (1.6, how many of pounds of raisins).
• It is helpful to put headings in the first row that describe what information goes in each column (and headings in the first column to describe each row). It is also helpful to organize the information in the spreadsheet to be read from left to right and from top to bottom.
• If you type a formula correctly into any cell of a spreadsheet, the program will calculate and display the value of the expression. Formulas must start with an equal sign. If the answer is not a whole number, the spreadsheet will display the value as a decimal. It is possible to program each cell to round decimal values.

Note that spreadsheets on tablets are a bit different.

• The “enter” key on the keyboard is usually at the lower right, marked with a right-angle arrow pointing down and left.
• The Numbers spreadsheet involves less typing. Instead of typing = and a formula, tap the = button; tap a cell whose address you want in the formula; type numbers and operations. If you have typed headings, the words will appear in the formula, such as Amount (lb) Cheese.

Provide access to spreadsheets. Give students quiet work time followed by whole-class discussion.

Some students may not understand what happened when they completed the last instruction. The fact that copying a formula from one cell and pasting it in another cell updates the row and column references in the formula can be counter-intuitive. Prompt students to double click on the cell where they pasted the formula so they can see what the formula now says.

If possible, display a spreadsheet program and go through the steps described in the students’ books or devices. Ask students to explain why each step has the result it does. (See student response for explanations.)

The most important things for students to remember from this activity are:

• Formulas in spreadsheets start with the equal sign and use * and / for multiplication and division, respectively.
• You can refer to the value in another cell of the spreadsheet within your formulas. For example, the formula =A3+B2 will display the sum of the values in cells A3 and B2, as long as those cells contain just numbers and no words.
• If you copy a formula from one cell and paste it into another cell, the program will automatically adjust any cell addresses in the formula by the number of rows and columns between the cells where the formula was copied from and pasted into.

In this activity, students use a spreadsheet program to compute the cost for one serving of each meal they want to serve at their restaurant. Students have to find or estimate the cost of each ingredient. Since ingredients are sold in different units, they have to convert units from the ones they find for the cost to the ones used in the recipe. For example, olive oil is sold in 1 quart bottles, but the recipe asks for tablespoons. The calculations can be done in the spreadsheet, and entering the formulas into the cells of the spreadsheet is an important mathematical step. Another important step in this activity is to plan the set-up of the spreadsheet. 

If possible, have students refer back to the recipes they selected in the previous lesson. Otherwise, provide recipes for students to refer to here. Give students quiet work time followed by partner and whole-class discussion.

Some students may struggle to know what formulas they should type into columns D, H, J, and K. Prompt them to write down on paper what calculation they would do for the first one or two ingredients and look for patterns that could help them figure out the other rows automatically.

Poll the class on the cost for one serving of their recipe.

Invite student to share their experiences using the spreadsheet:

• “What was the most difficult part of setting up your spreadsheet to do these calculations for you?”
• “What strategies did you use to help you decide on the formulas for columns D, H, J, and K?”
• “Which method do you prefer for this type of problem, using paper, pencil, and a calculator or using a spreadsheet? Why?”