Sample Lesson Spreadsheets

Back to: Investigating Functions using Spreadsheets || Math Units: Contents

Linear Functions - Working with y = mx


  1. Click on cell Y1 and observe the input bar at the top. You should see =m*X1. The equals sign denotes a formula in a spreadsheet. The value of m is taken from cell W4. The * sign denotes multiplication. X1 refers to the contents of cell X1.

  2. Try changing the value in cell W4 to 3. (To do this, put the cursor over the cell, type 3 and hit Enter.) Observe the changes in the Y column. The graph should also change to plot the new values from columns X and Y.

  3. Experiment with changing the value of m to larger, smaller, and negative values and observe the changes in the table and in the graph. (Don't worry about changing things; you can always reload the page.)

  4. In cell Z1 enter the formula =2*X1. Highlight cells Z1 to Z11 and under the Edit menu choose Fill, then Down. Notice that the command causes the table to update. Now the line should pass through the square at (5,10).

  5. Enter formulas in AA1 and AB1 so that the line passes through the points (6,38) and (8,-8) respectively.

  6. Record your observations and answer the questions in Question Set 1.

Linear Functions - Working with y = mx+b


  1. Click on cell Y1, to see the general equation. The equation y=mx+b is entered as the formula =m*X1+b.

  2. Notice that the value of m in box W5 is 1 and the value of b in cell W6 is 0. What is the equation of the line in the graph?

  3. In box W6, change the value of b to 2 and note the changes in the Y column and in the graph window.

    • What change do you notice?
    • Has the slope of the line changed?
    • Has the direction of the line changed?

  4. If m = 1 and b is now 2, what is the equation of the new line in the graph?

  5. Leaving the value of m alone, change the value of b to cause the line to pass through the coloured squares. When you succeed, write an equation for the new line.

  6. Using your new equations, type formulas in Z1, AA1 and AB1 and use the Fill Down command to plot the lines.

  7. Answer the questions in Question Set 2.

Introduction to Power functions -Working with y = axn


  1. Put your cursor over cell Y1 and see the formula for the equation. You should see =a*X1^n. The * sign is used for multiplication and the ^ sign denotes exponents. Values for a and n are stored in cells W5 and W6. Since a = 1 and n = 2, what is the equation of the curve in the graph?

  2. Record the y-intercept of the graph.

  3. Change the value of n until the curve passes through the red square. Remember, you can use decimals. Write the equation down.

  4. Repeat the procedure in 3 for the other squares on the graph.

  5. Using your equation from number 3, enter a formula in cell Z1 that will cause the graph to pass through (3,22).

  6. Using your equations from number 4, enter formulas in AA1, AB1, and AC1 that will cause the graph to pass through (64,42), (5,-25) and (4,-64) respectively.

  7. Answer the questions in Question Set 3.

Quadratic Functions - Working with y = a(x-p)2+q


  1. Set the cursor over cell Y1 to note the formula. You should see: =a*(x-p)^2+q. The ^ symbol is used for exponents in a spreadsheet and the * symbol must be used for multiplication. Note the values of a, p, and q stored in W5, W6 and W7.

  2. Try altering the values of a, p, and q. It is difficult to see how the parabola changes because the scale automatically adjusts. Therefore, set a = 1, p = 0 and q = 0 and proceed to number 3.

  3. Make up an equation in the form y=a(x-p)^2+q, and enter the formula in cell Z1. Then use the Fill Down command. For example, you could try =2*(X1-1)^2+1. (To see a larger graph you can touch the plot window and select full screen.)

  4. Record your values for a,p and q in a chart and also record the y-intercept, the vertex, and whether the vertex is a minimum point or a maximum point.

  5. Continue in columns AA, AB, AC, AD and AE to enter more equations, leaving p the same and changing a and q.

  6. After recording your findings, begin at column Z again and this time, change a and p but leave q unchanged.

  7. Finally, change p and q but leave the value of a unchanged.

  8. Answer questions in Question Set 4.

Please mail comments and suggestions to
Margaret Sinclair