Spreadsheet Strategies

Level/Course: Algebra I

Solving problems using an algebraic formula is a common topic throughout the algebra
curriculum. Once teachers and students master the syntax of entering equations in the
spreadsheet, it becomes a useful tool for many topics. This particular application
illustrates the Quadratic Formula.

Objectives: Students use the spreadsheet to solve a quadratic equation by the quadratic
formula.

Activities:

In each cell of the spreadsheet, there are three options for data entry: numbers entered
are treated as numbers, an entry preceded by the equal sign is a formula , and an entry
preceded by a quotation mark is a label. When entering a formula, the data in other cells
may be referenced by name A1, A2, etc. or by clicking in that cell to input it. A cell
referenced by A4 is considered relative, and in the next row, it would use B4. To keep a
value absolute (not dynamic), use the dollar sign. A$4$ would always use the value of
A4. Or A$4 would use the value of A4 or A5, etc. A large number of functions
(including math and statistical functions) are built into the program, and may be accessed
from Insert-Function.

The following example solves equations with the quadratic formula.

Assessment:

• Classwork and homework

Level/Course: Algebra 3/ Trigonometry: This lesson can be modified to fit any course or
level by changing the problems and answers.

Objective: Students will solve problems and be able to check their answers.

Activity:

Solve problems (first column) on a scrap sheet of paper, and then type in the answer in
the cell next to the question (second column). Press enter. If the answer is right, then
“Correct” will show up in the third column. Work all problems until they are all Correct.

Example:
 

Example 2:(The numbers in black are the answers to the problem)

Assessment
This use of a spreadsheet for practice with immediate feedback is an efficient and
effective means of assessment.

Level/Course: Geometry
This lesson fits with convex regular polygons. Learning how to compute and find
relationships between interior, exterior, and central angles is a major concept in
Geometry. It could also be used when starting the concepts dealing with apothems of
regular polygons. This lesson is designed for students to see the relationships between
these different angles.

Objectives:
Given the number of sides on a regular polygon, students will describe relationships seen
between angles.

Activities:
Enter the number of sides on a regular polygon and see how the angles relate. Excel will
display the interior, exterior, sum of interior, and central angles of that polygon.

Sample Output:

Assessment:
• Individual classwork and homework.
• Provided worksheet.
• Use chart for prediction.
• Writing exercise involving relationships of angles.

Angles in Regular Polygons Worksheet

1. What is always true about the relationship between 1 exterior angle and 1 interior
angle?

2. As 1 interior angle gets bigger what happens to the exterior angle?

3. As the number of sides change, how does the total sum of the interior angles
change?

4. What is the relationship between 1 interior and 1 exterior angle to the total sum of
interior angles?

5. What is the relationship between 1 interior angle and a central angle?

6. What is the relationship between 1 exterior angle and a central angle?

7. As the number of sides increase, what happens to each interior angle?

Level/Course: Discrete Mathematics

Objective: To solve problems involving permutations and combinations
There are some situations involving choices where the order in which those choices are
made is important. There also situations where the order is not important.
To find the number of ways n elements can be arranged in order we use permutation. A
way to help your students to remember that order matters for permutations is the example
of phone numbers, 555-2163 and 555-2136 are not the same and it matters the order you
dial the number. Combinations are counting problems where order does not matter. The
use of card games can help students remember combinations since the order the cards are
dealt does not change the “hand”.

Activity: Compute Combination:
Excel Steps:
1. Highlight a cell. (A1)
2. Type in: = combin(10,2)
3. Enter

Activity: Compute Permutation:
Excel Steps:
1. Highlight a cell. (B1)
2. Type in: = permut(10,2)
3. Enter

Activity: Compute Factorial: 8!
Excel Steps:
1. Highlight a cell. (C1)
2. Type in: = fact(8)
3. Enter

Level/Course: Algebra I or II
The topic of graphing quadratic equations is covered in Algebra I and Algebra II, but
similar procedures using spreadsheets to examine functions and their graphs could be
used in more depth in Algebra II.

Objectives: Given a parabolic function, students will be able to predict features of the
graph based on the sign of the leading coefficient and the value of the constant. Students
will understand the graphic effect of changing the coefficient of x², the coefficient of x,
or the constant.

Activities:
Enter the x-values and the function, allowing Excel to compute the y-values. Create an
xy-scatter plot and add a trendline to display a graph of the parabola. Change various
signs and values throughout the function to observe the resulting changes in the graph.

Excel Steps:

1. Enter x-values
2. Enter the function and direct it to fill in the y-value in the appropriate cell (ex. If the
first x-value is in cell A6, the function y = 4x² + 2x + 3 would be entered in cell B6 as
follows: =4*(A6^2)+2*A6+3)
3. Fill in the remaining cells in column B to compute the other y-values (the function in
B6 can be dragged into the other cells so Excel changes the formula to fill in A7, A8,
etc.)
4. Choose Chart
5. Select XY (scatter)
6. Finish
7. Chart – Add trendline – (Select polynomial of order 2)

Sample Output:

Assessment:
• Students will graph parabolas without any graphing technology based on the
features of the function discussed
• Students will enter functions into spreadsheets and create graphs to check the
graphs they created

Level/Course: Algebra I
Linear regression is easily accomplished in a spreadsheet in a multiple representation
format. The data are entered in a table, and the program presents a graphical and
algebraic representation. The same method works equally well for curve fitting in
nonlinear functions.

Objectives: Given a set of ordered pairs, students will graph the points, plot a leastsquares
regression line, and give the equation of the line.

Activities:

Enter the set of data points, plot them, and then plot a line of best -fit, called a trendline.
Excel will draw the least - squares regression line and give its equation.

Sample Output:

Assessment:
• Individual classwork and homework
• Group Projects with real-world data
• Use of the regression equation for prediction
• Writing exercises involving description of process and reflection on its use

Level/Course: Algebra II
This activity could be used at the introduction of a unit on matrix operations. Working as
a class or in groups, students could explore how these operations work .

Objectives: Given a matrix, students will learn how to find the scalar multiple and
determinant of the matrix. With a pair or matrices, students will learn how to multiply
them.

Activities:

Multiplication by a Scalar
Enter the matrix data and the appropriate equation. Excel will calculate the matrix ’s
scalar multiple for a given scalar.

Sample of Procedure:

Finding the Determinant
Enter the matrix data and mdeterm(array), the Excel equation for determinants, then
Excel will calculate the determinant of the matrix.

Sample of Procedure:

Matrix Multiplication
Enter the data for the two matrices then use Excel equation mmult(array1,array2) to
multiply the two matrices.
 

Sample of Procedure:

Assessment:
• Group project exploring the algebra behind matrix operations
• Using Excel to perform matrix operations
• Individual class work and homework
• Writing exercises describing discoveries about how matrix operations work

Matrix Operations Worksheet

Using Excel, follow the directions provided in class to perform the following matrix
operations:

Multiplication by a scalar:

5) How is the solution matrix related to the original matrix in the problem? What
patterns do you see?

Finding the Determinant:

5) What patterns do you see in how the determinant relates to its’ matrix?
How is the answer for the determinant for the matrix in problem 2 different?
What can you conclude from that?

Matrix Multiplication:

2) What patterns do you see in how the solution relates to the two original matrices
in the problem 1? How about in problem 2? Can you combine your ideas to make
a general statement for how to multiply any matrices?

Level/Course: Pre-Algebra, Algebra
This activity should be completed by students so that they can see how real-world
information/data can be put into graphs. This exercise is helpful because it allows
students not only to see and use real-world data and how it pertains to them, but students
are also able to utilize their statistical skills to interpret the information from those
graphs.

Objectives: Given a table of information and data, students will interpret the given
information and put in into a table. Then, students will be able to put this information
into any type of graph. These graphs help the students interpret the data and also to make
conclusions about the given information.

Activities: Gather data from a certain source (i.e. the Internet) and make a table
organizing the data. Then, plot the data onto a graph. Excel will make the graph with
various colors, appropriate titles, and a key to help understand the graph.

Sample Output:

Assessment:
• Individual classwork and homework
• Group projects with real-world data (i.e. Web Quests)
• Written exercises involving a understanding of the graphs and interpretations of
the given/collected data

Level/Course: Algebra II

Objectives: Given a function and a range of x values, students will use spreadsheets to
obtain corresponding y values, graph the function, and find the zero ’s of the function.

Excel Steps
1. Enter x values
2. Define cells of x values as variables in the given equation.
• Highlight column of x values
• Insert-Name-Define (give variable a name, in this case, x)-OK

4. Graph function
• Highlight x and y values
• Insert-Chart- XY Scatter-Choose appropriate chart type- Finish

5. From the data and the graph determine approximately where zero’s occur.

6. Use “Goal Seek” to evaluate function at y=0 to find the values.
• Highlight appropriate y-value cell. (the one before each sign change in y, in this
case, cells B3, B8, and B9).
• Tools-Goal Seek- Enter 0 in the “To Value” Box, and corresponding x-value cell
name in the “By Changing Cell” Box – OK

Level / Course: Advanced Placement Statistics or Discrete Mathematics or Statistics

Objectives:
Statistics involves many calculations that once understood are easily calculated using
technologies. Technologies used typically include a TI-83 calculator, MiniTab, Fathom,
or SPSS. Often overlooked for this same purpose of calculating statistics is Microsoft
Excel. Excel is more readably available in the real world, hence why not teach students to
use Excel to perform statistical procedures?

Activities:
See attached sheets for some sample outputs from Excel. These are just a few of the
many calculations Excel will perform. For more commands, the “HELP” feature of Excel
is great when you type in ‘statistics.’

Statistical Test:
To perform statistical procedures such as t-test and z-test you will first need to load the
data analysis package.

*Note that this is preloaded on some, to check click on “TOOLS” on the menu bar. If you
see DATA ANALYSIS (and you can select it) you’re ready to go. If not, under the
TOOLS menu, cluck on “ADD INS” and select the “Analysis Took Pak” and then load.*

The following is one of many statistical tests Excel will perform.

To perform a two-sample t-test (or z-test, depending on if you are dealing with a
population or a sample):
• Click on “Tools” from the menu bar
• Select “Data Analysis”
• Choose the appropriate statistical test, t-test or z-test. (I used t-test: two sample unequal
variances assumed.)
• Enter the appropriate columns of data in the first two boxes. (Again you can use a colon
to indicate a series in a row or column.) IMPORTANT NOTE: You must enter the first
cell in these boxes as the cell above or before your first actual number! (see pictures
below)
• For Hypothesized Mean Difference enter 0.
• If you have given your data sets labels, click show labels.
• Click Ok!
• Your statistical test will appear as another worksheet on the tabs below.

Below are the screenshots of my two sample, unequal variance t-test:

Excel commands for Statistical Procedures:

Basics:

Pertaining to the Cumulative Distributive Function (CDF):

For a Chi-Squared Statistic: