Cost of a Meal

15 min

Teacher Prep
Setup
Explain what spreadsheets are and how they work. Access to spreadsheets. Quiet work time followed by whole-class discussion.
Required Preparation
For the digital version of the activity, acquire devices that have access to a spreadsheet program.

Narrative

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. This activity gives students the opportunity to explore how the structure of a spreadsheet can be used to solve problems (MP7).

This activity uses the Collect and Display math language routine to advance conversing and reading as students clarify, build on, or make connections to mathematical language.

Launch

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

  • A spreadsheet is a computer program that lets one organize information in a grid of rectangles, called cells, and 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.
     

    <p>A 4 coulumn spreadsheet with 3 rows of data.</p>
    A 4 coulumn spreadsheet with 3 rows of data. The heading for Column A is "Ingredient." The heading for column B is "Weight, in pounds." The heading for Coulm C is "Unit cost, per pound." The heading for Column D is Cost, in dollars." The data for the spreadsheet are as follows: Row 2: Cheese; 3 point 6 pounds, 4 point 5 zero dollars, 16 point 2 zero dollars. Row 3: Raisins; 1 point 6 pounds, 2 point 5 zero dollars, 4 point zero zero dollars. Row 4: Peanuts; 2 pounds, 3 point zero zero dollars, 6 point zero zero dollars.

    Ask the students, “What information is in cell B3? What does this piece of information mean in terms of the situation?” (1.6; how many pounds of raisins there are).
  • 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 a formula is typed 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 is wanted in the formula; type numbers and operations. If there are typed headings, the words will appear in the formula, such as “Amount (lb) Cheese.”

<p>A spreadsheet and a calculator on a tablet.</p>
A spreadsheet and a calculator on a tablet are indicated. At the top of screen, is a sample 4-column spreadsheet with column A titled, “Ingredient,” column B titled “Amount in pounds,” column C titled “Unit cost in dollars per pound,” and column D titled “Cost.” Row 2: Cheese, Amount in pounds, 2; Unit cost in dollars per pound, 4 point 5 0 dollars; and Cost, blank. Under the cost column, the formula for each cell shows “equals, Amount, in pounds, of cheese” times “unit cost, in dollars per pound, of cheese.” At the bottom of the screen, on the left are the following buttons : open parenthesis, closed parenthesis, comma, percent, multiplication, addition, to the power of, division, subtraction, ampersand, less than and equal to, and not equal to. In the center is a calculator with a number pad and to the left are the following buttons: functions, a b c, calendar, true/false, delete, right, and enter.

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

Use Collect and Display to create a shared reference that captures students’ developing mathematical language. Collect the language students use to discuss spreadsheets. Display words and phrases such as “cell,” “box,” “formula,” “number,” “row,” and “column.”

Student Task

  1. Type each formula into the cells of a spreadsheet program and press enter. Record what the cell displays. Make sure to type each formula exactly as it is written here.
    A B C D
    1 =40-32 =1.5+3.6 =14/7 =0.5*6

    1. Predict what will happen if you type the formula "=A1*C1" into cell C2 of your spreadsheet.

    2. Type in the formula, and press enter to check your prediction.

    1. Predict what will happen next if you delete the formula in cell A1 and replace it with the number 100.

    2. Replace the formula with the number, and press enter to check your prediction.

    1. Predict what will happen if you copy cell C2 and paste it into cell D2 of your spreadsheet.

    2. Copy and paste the formula to check your prediction.

Sample Response

    • Cell A1 displays the number 8 because that is the answer to the subtraction problem 40−3240-32.
    • Cell B1 displays the number 5.1 because that is the answer to the addition problem 1.5+3.61.5+3.6.
    • Cell C1 displays the number 2 because that is the answer to the division problem 14÷714 \div 7. Division is represented with the slash character in spreadsheet programs.
    • Cell D1 displays the number 3 because that is the answer to the multiplication problem (0.5)⋅6(0.5) \boldcdot 6. Multiplication is represented with the asterisk symbol in spreadsheet programs.
  2. Cell C2 displays the number 16 because it is multiplying the value in cell A1 times the value in cell C1 and 8⋅2=168\boldcdot 2=16.
  3. Cell A1 just displays the number 100 because a number was entered, not a formula. Cell C2 updates to display the number 200, even though nothing was done to that cell, because it is still multiplying the values in cell A1 and C1, and A1 is now 100 instead of 8, so 100⋅2=200100\boldcdot 2=200.
  4. Cell D2 displays the number 15.3 because it is multiplying the value in cell B1 times the value in cell D1, and (5.1)⋅3=15.3(5.1)\boldcdot 3=15.3. When the formula in C2 was copied and pasted into D2, it moved one cell to the right, so the letters in the formula automatically adjusted to refer to one cell to their right. In other words, A1 changed to B1, and C1 changed to D1. If the formula was pasted into a different row, above or below, the numbers as well as the letters in the cell addresses would have updated.
Activity Synthesis (Teacher Notes)

Direct students’ attention to the reference created using Collect and Display. 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.) Invite students to borrow language from the display as needed and update the reference to include additional phrases as they respond. 

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.
  • The value in another cell of the spreadsheet can be referred to within 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 a formula is copied from one cell and pasted 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.
Anticipated Misconceptions

If students find the last instruction counter-intuitive, consider asking:

  • “Tell me more about what happened when you copied cell C2 and pasted it into cell D2.”

  • “What is the same and what is different about the formula in cell C2 and the formula in cell D2?”

30 min