Equations with one variable
For example, let's say you have a long homework task which involves substituting lots of different numbers into an equation and evaluating it. We can use spreadsheets to help us with this task.
Let's say the homework task looks like this:
For the following functions, substitute the value for x and evaluate for x = 1 to x = 50.
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. The notation and behaviour of your spreadsheet may differ slightly from what is presented if you are using a different program. 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 for a few cells across. 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 "homework questions" 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
Now we are going to look at equations with two variables. A simple example which you are already familiar with is times tables. The answer to a times table question depends on both of the numbers, and this is often represented by a table.
Task 2
Your task is to create a spreadsheet which generates the timetables from 1 to 12.
Step 1: Start off 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 in your 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 your spreadsheet depending on how you have set it 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. If we look at the formula, we can see what it is. 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 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 your 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, here we have locked in column A in the reference. Now when we use the blue dot to extend the formula, it changes the B but the A stays the same.
However, we still run into a similar problem when we try to extend the formula vertically down to the other numbers:
If we look at the last formula we see that this time the numbers are the problem, as it ends up trying to calculate =B16*$A17 instead of =B7*$A17. We can fix this 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 won't change as we extend the formula. Now that this is locked, we can extend the formula down again and everything should works. We can extend the formula out to cover the whole table, and we end up with a correct multiplication table:
Step 3: Increase your 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
For the next task we will have a look at real life example and creating a table that gives a value based on the weight(mass) and height of a person. The body max index (BMI) is intended to be used a rule of thumb to assess whether or not a person is underweight, normal weight or overweight.
Task 3
Your 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 \frac{Weight}{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 you have created the basic table, see if you can improve it. For example, you could make the BMI round to the nearest whole number using the ROUND function or formatting conditions, or make your 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.
Task 4
Many professors, mathematicians and health professionals believe that there are limitations and flaws in the BMI measurement suggesting that it is not an accurate way of assessing whether or not an individual is at a healthy weight. Your task is to research and report on an alternative.
Step 1: Research an alternative to the body mass index.
Step 2: Create a spreadsheet showing the calculations from the alternative formula.
Step 3: Comment on the comparison.