Equations with one variable
The use of spreadsheets can expediate a task which involves the repeated substitution of different values into an equation.
This exploration uses spreadsheets to substitute values of x ,from x = 1 to x = 50, into these equations:
a. y = 5 x + 4
b. y = \frac{3 x}{2} + 4
c. y = x^{4} + \frac{3 x^{2}}{879} + \left(\frac{579}{213}\right)^{ - 4 }
Task 1
Your task is to use a spreadsheet application such as Google sheets or Excel spreadsheet to generate the answers for the questions above.
Note: This investigation was written using a particular spreadsheet software. There are many available, but the basic functions covered here are included in most programs. If you are using a different program, the notation and behaviour of your spreadsheet may differ slightly from what is presented. If in doubt, ask your teacher.
Step 1:
Open a new spreadsheet.
Create titles for the first a second row by entering x and y in the cells A1 and A2, respectively.
Step 2:
Fill in the first 2 numbers of the x row by placing 1 in cell B1, and 2 in cell C1.
Step 3: Writing out all the numbers from 1 to 50 for each table of values does get tiring. Rather than doing it manually, we'll get the computer to do it for us. Just click and drag over the cells containing 1 and 2 until they are both selected.
Next, click on the little blue dot in the bottom right corner of the cells you just selected. Now, select and drag this dot out across a few cells. This tells the computer that you want to continue this pattern across those extra cells. The computer sees that your pattern so far is 1, 2 so it figures that you probably want it to continue with 3, 4, 5 ....
In this way, continue your pattern until it goes from 1 to 50.
Step 4: We want to tell the computer which formula we want to use to calculate the y value. Select the first free cell in the y-row, B2, and then click on the input bar above, next to the fx. The first question uses the formula y = 5 x + 4, so we want it to take the x value, multiply it by 5, and then add 4. The x value is in the cell immediately above it, which in this case is the value in B1.
To do this, write the formula as: =B1*5 + 4.
Note: All formulas start with an = sign, so that the computer knows that you want it to calculate and not just write out your formula in that cell. When you press Enter, the calculation will be performed and the value placed in the cell, which in this case is 9.
That might have seemed like a lot of work for a calculation you could have done pretty quickly, but that magical little blue dot allows us to copy this result to other cells, saving us a lot of time. As you did before, just click the blue dot and drag it over until it reaches the end of the row, and all the other cells will follow the same pattern. If you click on the D2 cell and look at its formula, you'll see that the computer has altered the cell reference from B1 to D1 so that you don't have to change this every time.
Step 5: Create the formulas for the other equations in the rows below. Note that for powers you need to use the ^ symbol (shift + 6). Also make sure you bracket negative numbers and fractions carefully, just like you do on a calculator, as it is easy to make a mistake this way.
Step 6: Write your own complex formula for y in terms of x and create a table of values for x=1 to x=50 in your spreadsheet,
Equations with two variables
The second task explores equations with two variables. Calculating times tables is a simple example that depends on two numbers.
There needs to be a space between left and most.
Task 2
The task is to create a spreadsheet that generates the timetables from 1 to 12.
Step 1: Start by filling in 1 to 12 across one row, and 1 to 12 down the leftmost column as shown:
Step 2: Using $ to lock parts of the formula.
Fill out the formula in the first cell and extend it across the spreadsheet. In this example spreadsheet, the formula would be =B7*A8, but the addresses may be different in the spreadsheet depending on how it has been set up.
For our 1 \times 1 result cell we get an answer of 1, which seems to be working well. However, we run into problems when we try to extend this using the magic blue dot:
Since 1 \times 10 does not equal 3628800, something has gone a bit awry. Looking at the formula, the issue can be determined. The computer, trying to be helpful, has changed the letters to =K7*J8, moving the letter across to match the current cell. However, while we want to use K7 it as our first number, we still want to keep A8 as our second number. To stop the computer from altering the reference, go back to the original formula and put a $ sign before the A, so that the formula reads =B7*$A8. The $ sign can lock in a row and/or column reference. Column A has been locked in the reference. When the blue dot is used to extend the formula, it changes the B but the A stays the same.
However, the same problem is experienced when we extend the formula vertically down to other number:
Looking at the last formula it can be seen that this time the numbers are the problem, as it ends up trying to calculate =B16*$A17 instead of =B7*$A17. This can fixed by putting a $ sign in front of the number, so that the top-left formula becomes =B$7*$A8. This means that the 7th row is now locked, and will not change as we extend the formula. Now that this is locked, we extend the formula down again and everything should works.
We extend the formula out to cover the whole table, and create a correct multiplication table:
Step 3: Increase the table to 20 \times 20 .
Step 4: Generate a table showing the results of the numbers 1 to 5 raised to powers of 1 to 5 .
Real-life equations
In the next task, a real-life example will be explored by creating a table comparing the weight and height of a person. The intended use is to assess whether or not a person is within a healthy weight range.
Task 3
The task is to create a spreadsheet to display the BMI values based on given heights and weights.
Step 1: Open a new spreadsheet. Starting in cell B1 fill the row with heights in metres. Type 1.1 in cell B1 and 1.2 in cell C1 and then fill across to 1.8 m.
Step 2: Starting in cell A2 fill the column with weights in kilograms. Type 50 in cell A2 and 60 in cell A3 and fill down the column to 110 kg.
Step 3: The formula for calculating BMI is \dfrac{\text{Weight}}{\text{Height}^2}, where weight is in kilograms and height is in metres. Use this formula to complete the spreadsheet with all the BMI numbers.
Step 4: Once the basic table has been created, try to improve it. For example, make the BMI round to the nearest whole number using the ROUND function or formatting conditions, or make the spreadsheet colour-code the BMI into broad categories (underweight, normal weight, and overweight) using conditional formatting. Mastering spreadsheets has wide-ranging applications well beyond budgets and column graphs. Explore further functionality when you have the opportunity.