Digital Maestro Magazine Google Sheets Graph Math Functions Linear and quadratic equations
Graph equations and learn basic spreadsheet operations
digitalmaestro.org
June 15, 2018
Digital Maestro Magazine • Google Sheets to Graph Linear Equations
TABLE OF CONTENTS LINEAR EQUATIONS
4
GRAPHING LINEAR EQUATION
5
LINEAR EQUATION TEMPLATE
13
GRAPHING QUADRATIC FUNCTIONS
19
QUADRATIC FUNCTION TEMPLATE
25
Page 3
Google Sheets to Graph Linear Equations • Digital Maestro Magazine
Linear Equations
them. Schools or teachers may not be willing to allow students to take graphing Linear equations represent a pattern calculators home. of numbers. These numbers are either increasing or decreasing. The begin- Another reason for using a spreadning point and the amount by which it sheet instead of a calculator is that moves is the y-Intercept and the Slope. spreadsheets are shareable. Students The slope is the amount by which the use Google Sheets to create their line is moving. A linear equation looks graphs and submit them to the teacher. much like this equation “y=mx+b”. The Multiple equations can be graphed on variable “b” represents the Intercept, or one spreadsheet with each graph being where the line begins. The variable “m” placed onto a separate sheet in the represents the slope or the amount by workbook. For example, a teacher can which the line changes. Linear equations assign twenty problems and students and the graph of these equations can be would use twenty sheets in one workused to help make predictions. Graphing book to graph each equation. All spreadthe equation can give us a better sense of sheets share the basic math functions patterns in the data. we will be using in these lessons. The instructions in these lessons are appliSpreadsheets cable in Excel and other similar spreadsheet applications. S preadsheets are very useful and are used for a variety of reasons. They are often used to collect and organize data. Spreadsheets are used to gather and make sense of fnancial information. They are often used in fnancial institutions. Spreadsheets are also used by scientists and mathematicians to organize and make sense of data. One way of making sense of large data is to generate a graph. We will be generating a visual representation of a linear equation and the data points used in that equation. Why Spreadsheets and not graphing calculators? Graphing calculators are an excellent tool in classrooms but they are expensive and not all schools can aford Page 4
Digital Maestro Magazine • Google Sheets to Graph Linear Equations
Graphing linear equation
C reate
a new spreadsheet in google sheets. Here are some helpful links to help get you to the location when you can create a spreadsheet. This link, “https://sheets.google.com” will take you to the Google Sheets application page where you can click the create button to create a new spreadsheet. This link, “https://sheets.google.com/create” will take you right to a brand new spreadsheet. Login to your Google Account for the sheet to be created.
Spreadsheets consist of a series of sheets. These sheets are part of a document called a workbook. The name we just provided this fle is for the workbook and not for an individual spreadsheet. A workbook can have hundreds of sheets. Each sheet contains a series of columns and rows. Each column and row has a rectangular box. This box is called a cell. Each cell is referenced by its coordinate location. The frst cell is referred to as cell A2. To fnd cell C3 we would look at column C and count 3 rows down. The letter and number combination is called a cell reference. We will be using cell references in the creation of our linear graph.
I like to provide a descriptive name to my documents right from the beginning. It’s a habit I learned early in my computer experience when fles were Preparing the equation not automatically saved until you saved them. Click on the “Untitled spreadsheet” C ells are versatile. They are designed to contain text, numbers, formulas, funcname. tions, images, and links to web pages. In Untitledspreadsheet the frst cell A2, type Basic Linear Graph. File Edit View Insert Format Data Tools Add-ons Help 100%
fx A
B
...
I
s
%
.o... .o~
123 .
A ri al
Basic Linear Graph File C
D
Edit
View
Insert
• Format
Data
Tools
Add-ons
100%
•
$
%
.o~ .o~
123 •
fx A
Basic Linear Graph
Title the document “Basic Linear Graph”. Google Sheets does not have a save button because all changes are automatically saved.
Page 5
Help
Allch
E
B
C
D
Aria l
I
E
Google Sheets to Graph Linear Equations • Digital Maestro Magazine The text we just typed appears in the cell. The text happens to be longer than the width of the cell. This is fne because the cell temporarily expands to accommodate the text as we type it. The cell will continue to expand as long as we continue to type within the cell.
Pressing the tab key will select the cell to the right of the previously selected cell. Type the equation y = 2x + 1 and press the return key. Basic Linear Graph File
Edit
View
,8, .......,...,.'P
Inse rt 100%
•
Format
. s
Data
%
Tools
.o.
.o~
Add-o ns 123 •
Help
Allch
A ri a l
fx
J_ Above the column letters we have a Basic Linear Graph Equa tio n y = 2x + 1 long bar. This is called the formula bar. 1 l The text that appears in our selected cell appears in this bar. The formula bar is a convenient location to edit cell text or as the name implies, cell formulas. Press Pressing the return key will select the the return key on your keyboard or click next cell in the current column. Press the return key one more time, type a capital on the cell below the text we just typed. letter “X”, and press the Tab key to move The cell below the previous cell is to the adjacent cell. This is where we will selected. Selected cells are highlighted enter the diferent values for our line. with a blue border. Type the word Equa- The slope of the line is “X” in our equation and press the tab key or click the cell tion. Type a “Y” in the adjacent cell. Select the cell below the “X”. to the right. B
A
C
E
D
2 3 4
-
5
Basic Linear Graph File
@I
Edit
View
.......,...,.'P
Inse rt 100%
• Fo rmat
. s
Data %
Tools
.o.
.o~
Add -ons 123 •
Help
Savi n
A ri a l
fx A
B
C
Basic Linear Graph 2
Equa tio n
5
l
D
E
Below the “X” we will supply several values that will be used in our equation. These numbers and the resulting answers in the “Y” column will provide the coordinates to connect in our graph. Enter the numbers 1 through 4 down the column with one number in each cell. fx A
B
Basic Linear Graph
y = 2x + 1
Equa tion
2 3
y
X
4
5
1
6
2 3
7
a
Page 6
T
9
,-1
10
I
--~!
4
C
D
I
E
Digital Maestro Magazine • Google Sheets to Graph Linear Equations Below the letter “Y” we will be inserting our equation. Select the cell below the letter “Y”. Equations in cells must begin with an equal sign. Type an equal sign in the cell. fx B
A
C
D
E
Basic Linear Graph 2
y = 2x + 1
Equation
3
y
X 5
1
6
2
I=
3
We entered all the numbers that represent the variable “x” in the column to the left. In the next step we will reference the numbers in the adjacent cell. Type A5 after the asterisk. This combination is referencing the cell where I have the frst variable in my equation. Notice that the reference cell is highlighted in orange. The reference to the cell is also in orange.
4
8
fx
10
=2• A5 A
C
B
D
Basic Linear Graph
The equation will mimic the linear equation we wrote above. Type the number 2 after the equal sign.
2
I
E
y = 2x + 1
Equation
:f 3
:::::::: p...._ :2 _*_A _5__
7
3
8
4
~
9
In the equation we know that 2x is actu- 10 ally 2 multiplied by the variable “x”. Simply placing the letter “x” next to the Finish the equation by entering the plus number 2 will not multiply the number symbol followed by the number 1. Press and the variable. We need to instruct the return key to fnish the process. the spreadsheet to multiply the number fx =2• A5+1 2 by the variable “x”. The multiplicaI Basic Linear Graph tion symbol is the asterisk. The asterisk Equation y = 2x + 1 on the keyboard is located above the number 8. Hold the shift key and press the number 8 to place an asterisk after 3 4 the number 2. A
B
C
D
E
2
:f:::::::: ,:,. ,.., 3
7 8 9
10
fx B
A
Basic Linear Graph 2
y = 2x + 1
Equation
3
y
X 5
1 1=2•
6
2 3
8
10
Page 7
4
j
C
D
E
The value for “Y” is 3. We can repeat the same process for getting the value of “Y” from the other values for “X”. The process is simple but can get tedious if we have lots of numbers.
Google Sheets to Graph Linear Equations • Digital Maestro Magazine Spreadsheets are powerful and they are designed to help process repetitive tasks. Click once on the cell with the equation we just created. The lower right corner of the cell includes a square when it is highlighted. Move the mouse pointer over the square until it changes to a “+” plus symbol.
Click once on any of the cells where the formula was duplicated. Look at the formula bar. The reference cell in the formula matches the adjacent cell where the formula is applied. @I
......,..,. 'P'
100 %
. s
%
.o .o~
123 •
fx D
C
B
Basic Linear Graph
fx
C
B
A
D
E
2
Equa tion
y = 2x + 1
X
y
y
X
4_
3
5
1
6
21
5!
7
3
7
8
4
9
3
1 ,_!____ 2
E
3 4
Basic Linear Graph
I
y = 2x + 1
Equa tion
2
=2* A5+1
Aria l
3
When we change the numbers in the reference cell for the “X” variable the values for “Y” update.
4 10
Click and drag this plus symbol down until the highlighted cells match the Creating the Line Graph numbers in the adjacent cell and release A line graph uses two points represented the mouse. by the numbers in our table. We need to fx =2* A5+1 select these numbers before inserting a Basic Linear Graph graph. There are multiple ways to select 2 Equa tion y = 2x + 1 content. I will review a couple of the y X ways here. Click once on the cell with the 2 5 letter “x”, press and hold the shift key on 3 7 4 9 the keyboard and click once on the last number for the values of “Y”. A
B
C
D
E
3
5 6
8 9
'LJ
10
The values for “Y” will be calculated using the same equation.
@I
fx
......,..,. 'P'
100 %
A
B
. s
C
Basic Linear Graph
4
5
T
IX
J
6 7 8
Page 8
y = 2x + 1
Equation
2 3
%
.o .o~
123 •
Aria l
X
r
IY 1
3
2 3
7
4
9
5
D
I
E
Digital Maestro Magazine • Google Sheets to Graph Linear Equations Clicking the frst cell and then clicking the last cell while holding down the shift key is how to select contiguous cells. This works with almost all applications not just spreadsheets. Contiguous means next to or touching.
A chart editor panel will appear on the right side of the spreadsheet. The frst option in the chart editor is the chart type. Click on the chart type selector. Chart editor
re •
I
X
DATA
CUSTOMIZE
K
There is a button bar option for inserting ..Ji Column chart a chart but this button might not be readily visible if you are using a computer None with a small display. Instead of using this button we will go to the menu and click Insert. The option to insert a chart is located about two thirds of the way A list of available chart types will be displayed. Our frst intuition might be down. to select the line chart type. This makes 2 New sheet Shift+F11 sense but I’m going to ask you to select 3 4 a diferent chart type which will be more :Hi+Option+M Q Comment Shift+F2 Note useful. Cha rt ty pe
Stacking
Data range
10
11
I
12 13
Function
Scroll down the list of chart types and look for the Scatter chart types. Click on the frst scatter chart type.
[;l;) Chart ...
14 15
l!!IImage ...
16
c-:::, Link ...
17
1B
illlK
Form ...
Google Sheets will read the data and make the best guess as to the type of graph it should create. It created a bar chart in this example. We will change the chart type.
Pie
Cf l [(j] [ iJ Map
F''-----'
v
Page 9
j
Yvs . X
"
.,
I·
•I
The scatter chart type will place a dot where each of our coordinate points meet. This is a little more useful than a simple line. If you would like to include a line that connects these points we can include that too.
Google Sheets to Graph Linear Equations • Digital Maestro Magazine To include the line we need to go into The Trendline is the same color as the the customize section of the chart editor plotted points. We can change the color panel. Click on the Series option. of the line by clicking the line color selector and choosing a color from the DATA CUSTOMIZE color palette. l K
Chart style
V
Chart & axis ti11es
V
Series
V
II
Dat a labe ls Trend line Linecolor
Type
~·
Linear Legend
V
Horizontal axis
V
Place a checkmark on the Trendline option. Pointsize
7px
II
Circle
Error bars Dat a labels Trend line
Type
Linecolor
Linear
~·
Line opacity
Linethickness
The chart will update to include the Trendline that goes through the points. Yvs . X
e v
----· · . ----·----•
Page 10
1owR'
Chart style
V
Chart & axis titles
"
Cha rt t itl e •
Type
Title text
:,..----Yvs. 10
:kness
The chart is titled according to the selected data series. Find the Chart & axis titles option and click to reveal the options. Click on the title feld and type the equation that is represented by this line graph.
Point shape
•
X
Title font
Title font size
Roboto
Auto
Click the alignment option and center align the title above the chart. The graph of our linear equation is now complete.
Digital Maestro Magazine Thank you for reading these sample pages.
Purchase the full issue at‌
digitalmaestro.org/shop
The function of education is to teach one to think intensively and to think critically. Intelligence plus character - that is the goal of true education. Martin Luther King, Jr.