Using Excel to Support Elementary and Middle School Math

This document contains notes from Terry Hawthorne's 2007 Southwest Virginia Technology Splash workshop on Excel and math.

Why Use a Spreadsheet in Math Class?

Solve Simple Word Problems

Jill purchased the following items at the store:

ItemCost
Milk$3.49
Bread$1.49

If she gives the cashier a $20 bill, how much change will she receive?

Questions to Consider

  • If you entered the costs with dollar signs, how does Excel store the number?
  • What is the purpose of labeling the amounts you use in your calculations?
  • In what way are the formulas you created similar to equations?
  • Modify the Problem

    Jill is no longer shopping for her family; now she's a shopper for a restaurant. Use the following information to determine what the total invoice for the restaurant will be, if Jill purchases the following items:

    Tip: Copy and paste the following table into Excel.

    ItemQuantityCost
    Milk12$3.49
    Bread16$1.49
    Orange Juice8$3.99
    Eggs6$0.99
    Flour5$1.99
    Butter3$2.49

    Modify the Problem, Part 2

    Wholesalers sometimes offer a small discount to customers who pay promptly. Assume that the supplier from whom Jill purchases her food offers a 2% discount on invoices paid with five days and a 1% discount on invoices paid within 10 days? What would the invoice be if Jill's restaurant pays within five days? What would it be if Jill's restaurant pays within 10 days?

    How would you modify the spreadsheet so that Jill can easily compare the savings resulting from applying different discounts?

    Basic Operations

    OperationSymbolExample
    Addition+=A1 + B1
    Subtraction-=A1 - B1
    Multiplication*=A1 * B1
    Division/=A1 / B1
    Exponentiation^=A1 ^ B1

    Excel performs operations in the following order:

    1. Negation
    2. Exponentiation
    3. Multiplication and division
    4. Addition and subtraction
    5. Concatenation (combining two strings of text)

    When operations are at the same level, such as multiplication and division, Excel performs operations from left to right.

    You can change the order of operation by using parentheses to group the operations you want to perform first; e.g.:

    3+2*5 results in 13

    (3+2)*5 results in 25

    Practical Application of Order of Operations

    The rule for converting degrees Celsius to degrees Fahrenheit is to multiply the Celsius temperature by 1.8, then add 32 to the result.

    Create a spreadsheet that converts Celsius values in column A to Fahrenheit values in column B.

    Then create a formula that converts the Fahrenheit values in column B to Celsius values in column C. When you perform the Fahrenheit-to-Celsius conversion,you will have to use parentheses to override the default order of operation: =(B1-32) / 1.8

    Dividing by Zero

    Here is a way you can use Excel to help students understand a fairly abstact concept: Why we can't divide by zero.

    1. Put any number in cell A1.
    2. Put 0 in cell B1.
    3. Write a formula to divide the value in cell A1 by the value in cell B1.

    What is the result? Why do you think Excel gives that result?

    To help students understand why division by zero is undefined, try this procedure:

    1. Set up a table like the following:
      A1B1
      1010
      101
      100.1
      100.01
      100.001
      100.0001
      100.00001
      100.000001
      100.0000001
      100.00000001
    2. In column C, write formulas to divide the value in column A by the value in column B. You will have to adjust the widths of columns B and C, and format the numbers in column C so that they display with thousands separators.
    3. Ask your students what is happening to the numbers in column C. What happens as you get closer and closer to dividing by zero?
    4. Charting

      Use the following table to construct a chart:

      StudentFavorite Food
      AmyPizza
      RobertHot dogs
      TamikaPizza
      EricHamburgers
      SarahPizza
      ChelseaPizza
      DavidHot dogs
      TimothyHamburgers
      JohnPizza
      MichaelHot dogs
      HaleyHamburgers
      CathySteak
      KristenFish sticks
      AngelaPizza
      LyHot dogs
      EnriqueHamburgers

      To create the chart, you will need a table that summarizes the results of the students' favorite food table.

      FoodCount of Students
      Fish sticks1
      Hamburgers4
      Hot dogs4
      Pizza6
      Steak1

      Tip: Counting the number of students who selected each food is tedious and can lead to errors. In any kind of real-world situation, where you have lots of data points, you should use Excel's COUNTIF function to count for you:

      1. Select all the values in the column of favorite foods, except for the header.
      2. Click Insert/Name/Define. Call the range of values something like FavFoods.
      3. Enter the following formula beside each food in the summary table: =COUNTIF(FavFoods,X??). This assumes that you named your range of favorite food values FavFoods. The X?? refers to the cell address that contains the label for the food you are summarizing on the row: Fish sticks, hamburgers, etc. This formula will count the number of times the students selected the food in cell X??. You can copy and paste that formula into the remaining cells in the summary table.
      4. Select the summary table and click Insert/Chart... Select an appropriate chart type and follow the prompts to complete your chart. If you selected a pie chart, your chart might look like this:

      Pie chart of students' favorite foods

      Tip: You can save your chart in GIF format for use on Web pages or in PowerPoint presentations.

      Additional Problem Solving Practice

      Here are some problems your students can practice once they get the hang of using spreadsheets to solve word problems.

      Shaq-a-Stack
      USB flash drives have replaced floppy diskettes as the medium of choice for removeable computer storage. How high a stack of floppies will a one-gigabyte (GB) flash drive replace? To solve this problem, you need to know that a 3.5-inch floppy diskette is 1/8-inch thick and will hold 1.44 megabytes. A one-gigabyte flash drive holds approximately 1,000 megabytes, or about 1 billion bytes. A byte consists of 8 bits. A bit is the smallest unit of digital storage. Think of a bit as a super-fast, super-small light switch. It can represent on or off, 0 or 1, true or false, negative or positive. Combine 8 bits and you get a byte, which can represent 256 different values, or 2 to the 8th power.
      iPod Advertising
      According to Apple Computer, the iPod Shuffle 1GB model will hold 240 songs. What is the average size of each song? Is 240 songs a reasonable claim?
      Google Racks Up Big Numbers
      Ask your students to open Google, and enter the name of one person. The object of this activity is to come up with a name that generates the largest number of links. For example, Elvis will usually generate over 17,000,000 links. Ask the students to record each name and the number of links it generates in a spreadsheet. Sort from smallest to largest or graph the results.
      Roll the Dice
      How many snake-eyes (2) would you get if you roll a pair of dice 1,000 times? The Java applet on this Web page will give you the answer. You plug in the number of rolls, and it very neatly graphs the results. You can perform a similar activity with this Excel spreadsheet. You have to enable macros on the sheet or it won't work.
      Bill Gates Gives You a Car Plus $200/Week Spending Money!
      This is a problem without a correct answer. It requires the students to perform various calculations to quantify the costs associated with driving a variety of very desirable cars.
      How Fast Was That Motorcycle Going?
      On September 8, 2004, Minnesota police ticketed a motorcyclist for going 205 miles per hour. A Minnesota State Patrol airplane pilot noticed the motorcylist going very fast and timed his distance between two quarter-mile markers on U.S. Highway 61 in southeastern Minnesota. Can you set up a spreadsheet that will convert the quarter-mile time to miles per hour? To solve this problem, your students have to understand that a vehicle's speed is a ratio representing distance traveled over a unit of time.