Spreadsheets and Substitution (Investigation)

Maths is hard sometimes. Sometimes it's hard in a good way, where it gives you challenges which you can solve creatively and give your brain a workout. Other times it's hard in a bad way, by making you do endless calculations which make you want to fall asleep. The good news is that these days, people who use maths in their day-to-day jobs don't have to sit there with a pen and paper and write out endless calculations, as computers are able to do most of the hard work for you! 

Unfortunately, computers don't yet have the ability to do all of the work. You still need to work out what you want the computer to do, and spend a bit of time trying to write that down in a program that the computer will understand. However, once you learn how to do it, you'll find it a lot easier than working it out yourself! 

Equations with one variable

For example, let's say an evil maths teacher has set you have a long homework task which involves making a table of values for a graph. The sheet looks like this:

For the following functions, plot a table of values for x = 1 to x = 10. 

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 }

Rather than do these by hand, or with a calculator, we'll show you how to do this the quick way using online tools. 

For this investigation, we'll be using Google Sheets, a free online tool. As Google Sheets is entirely based online, you can start your work on one computer and finish it on another, and it is impossible to forget to save your work as it automatically saves every few seconds. For those who have Microsoft Excel installed on their computers, you can use this too, the instructions are exactly the same (just don't forget to save!). 

First, log in at this website. If you don't have a Google account, you can make one for free here. Once you have logged in, click the green circle in the bottom right hand of the screen to create a new spreadsheet. 

You should now be in a screen that looks like this:

What you are looking at is a bunch of empty boxes, just waiting to be filled in with text. These boxes are called "cells". Each cell has an "address", which is given by the column (written in letters at the top) and the row (written in numbers along the side). For example, the cell in the top-left has the address A1. To enter text, just click on a cell and start typing. To start off, we're going to write "x" and "y" in the cells A1 and A2 as our row labels, and then fill in the first 2 numbers of the x row by writing "1" in cell B1, and "2" in cell C1

Writing out all the numbers from 1 to 10 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, click 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 10. 

Next, we want to tell the computer which formula we want to use to calculate the y value. Select the first cell in the "y" row, B2, and then click on the bar up the top, next to the "fx". "fx" stands for "function", and is the place where we put formulas when we want the computer to do some calculations for us. 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 B1. 

So we want the computer to take the value which is in B1, multiply it by 5 and then add 4. The way to write this as a formula is: =B1*5 + 4. Note that 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 will be calculated automatically, 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 heaps 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 wil follow the same pattern. If you click on the D2 cell and look at it's formula, you'll see that the computer was smart enough to change the "B1" in your formula to "D1" so that you don't have to change this every time. 

Have a go at doing the other "homework questions" in the area below in exactly the same way. As you can see from question c, even very difficult questions are very easy for computers to do, which saves you a lot of work in doing them yourself! Note that for powers you need to use the "^" key (shift + 6) and for fractions you use the "/" key. 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. 

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" depends on both of the numbers, and this is often represented by a table. 

Start off by filling in 1 to 10 across one row, and 1 to 10 down the leftmost column as shown: 

Next, fill in your formula in the first cell. 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 " 1 \times 1" 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 terribly wrong. 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, and the computer has messed this up by changing it to A8. To stop this from happening, go back to your original formula and put a "$" sign before the A, so that the formula reads "=B7*$A8". This $ sign means "don't change this value". Now when we use the blue dot to extend the formula, it changes the Bbut the Astays the same.

However, we still run into problems 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 7 is now fixed, and won't change as we extend the formula. Now that this is fixed, 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 perfectly good multiplication table:

See if you can increase your table to 20 \times 20  or more. 

A Real Life Example - Body Mass Index (BMI)

Obviously, spreadsheets were not designed to help students do their maths homework faster. Let's have a look at a real life example that goes beyond the maths classroom, using our spreadsheet to calculate BMI. 

The formula is: \frac{Weight}{Height^{2}}, where weight is in kg and height is in m. 

Have a go at generating a rectangular array of values for BMI using the skills that you learnt in the previous examples. You should be able to get something that looks like this, with height along the x-axis, weight down the y-axis and the calculated BMI for each particular height/weight combination in the middle:

Once you're done with the basic one, see if you can improve it. For example, you could make the BMI round to the nearest whole number using the ROUND function, or make your spreadsheet colour-code the BMI using conditional formatting. Once you've figured out how to unleash the full power of your computer, there's nothing you can't do!