The Arizona STEM Acceleration Project

Building the Distance Formula in Spreadsheets

Building the Distance Formula in Spreadsheets

A 7-12 grade STEM lesson

Emily Bogusch

6/1/2023

Objectives:

In this lesson we will:

• represent algebraic functions in a spreadsheet
• create a spreadsheet “calculator” for the Pythagorean Theorem
• transform the Pythagorean Theorem “calculator” into a distance formula “calculator”

Relevant Standards

​

​

Arizona Mathematics Standards

F-BF.A: Build a function that models a relationship between two quantities.

A-SSE.B: Write expressions in equivalent forms to solve problems.

​

Arizona Educational Technology Standards

Standard 5, Computational Thinker: Students develop and employ strategies for understanding and solving problems in ways that leverage the power of technological methods to develop and test solutions.

• Spreadsheet program (i.e. Excel or Google Sheets)
• Notecards or blank paper
• Ruler

List of Materials

Pacing and grouping

Depending on your students’ previous use of spreadsheets, as well as your own, this three-part lesson can take between 1 and three class periods.

You should be familiar enough with spreadsheets that you can guide, teach, and correct students as they learn.

Students will actively work on their own spreadsheets as you guide the class through the lesson. It is important that students work individually. However, allow students to ask each other questions and share their work.

Agenda - what will students do?

• Students examine differences in standard math notation and the conventions of spreadsheets (slides 8-9)
• Students input formulas in their own spreadsheet using spreadsheet conventions for math operations (slides 10-12)
• Students create a right triangle on a notecard and measure each side (slides 13-14)
• Students create the Pythagorean Theorem formula in the spreadsheet and test it with their measurements (slide 15)
• Students create a triangle using two points on a piece of graph paper. They reflect on how the side lengths can be computed using the coordinate pairs for the two points (slides 16-17)
• Students create their distance formula calculator. (slide 18)

​

Part 1: Algebraic representations in spreadsheets

The language of spreadsheets� �What do you notice?

Teacher Note:

An essential difference between standard math notation and spreadsheets involves the treatment of variables (or, inputs and outputs). Instead of variables, spreadsheets refer to cell locations. Students often struggle with this shift.

The language of spreadsheets� �What do you notice?

Formula typed in cell A2

The language of spreadsheets� �What do you notice?

When I type in some values for x and z (2 and 10), the answer (0.0764) appears in the cell where the formula was typed

You try it!

Teacher Note:

As students work, make sure to click on the cells as they work and check their equations. When students struggle with spreadsheets, they might type the target answers in the cells rather than using formulas. Teaching and learning how to use spreadsheets takes patience for both teachers and students.

Part 2: Programming mini-calculators

Objective 2: In this lesson we will create a spreadsheet “calculator” for the Pythagorean Theorem

Why? Spreadsheets are often used to store, organize, represent, and analyze large amounts of information. However, spreadsheets can also be used to systematize complex calculations.

One of my favorite sites, “CalculatorSoup,” has a variety of different pre-programmed calculators for mathematical, scientific and financial applications. We will create our own pre-programmed calculator.

Test it! Pythagorean Theorem

1. Use your blank notecard and a ruler to create a right triangle:

​

​

​

​

​

​

​

​

• Measure the two legs and hypotenuse of your triangle, to the nearest millimeter.

​

• Enter the two sides of your triangle into the CalculatorSoup Pythagorean Theorem Calculator. Did it calculate your measured hypotenuse?

​

• Now it’s time to create our own calculator!

​

​

Make your calculator user-friendly!

Use colored cells, borders, and labels so it is easy to use your calculator.

​

This is important if you are sharing your work with others. It is also important in case you want to use your file in the future (it is easy to forget what goes where!).

​

In my example, the formulas will go in the yellow boxes.

Teacher Note:

The challenging part of the Pythagorean Theorem formula for students will be the squaring and the square root. I teach students both the spreadsheet “SQRT()” formula and taking values to the one-half power.

Part 3: Deriving the Distance Formula

Objective 3: Transform the Pythagorean Theorem “calculator” into a distance formula “calculator”

Why? If you Goggle “Pythagorean Theorem calculator,” chances are one will pop up in the search results. However, it’s unlikely that you will need the Pythagorean Theorem for advanced schoolwork, your workplace, or personal finance.

Instead, you can use spreadsheets to build simple formulas customized to specific tasks. That’s what you will be doing for this task.

​

​

Using coordinate points for calculations

• On a sheet of graph paper, create an axis and two points. Label the coordinate pairs for each point.
• Draw a right triangle using the two points
• Using your Pythagorean Theorem calculator, determine the hypotenuse of the triangle.

​

How can you use the coordinate pairs for each point to determine the legs of the triangle?

Teacher Note:

This part of the activity is where students make the connection between the well-known Pythagorean Theorem and the complicated-looking distance formula. Look for students to individually have that “aha!” moment.

​

We use the terminology “delta” for change in.

Example Layout: Distance Formula Theorem

Teacher Note:

This part of the activity is where students make the connection between the well-known Pythagorean Theorem and the complicated-looking distance formula. Look for students to inidividually have that “aha!” moment.

​

We use the terminology “delta” for change in.

Assessment 1: Working spreadsheet

​

• On a piece of graph paper, draw a 4-quadrant coordinate grid. Draw and label two points on your grid; they should be in different quadrants.
• Draw a line between the two points. Measure this line in millimeters.
• Using your distance formula calculator, enter the coordinate points and determine the distance between the two points. Save the file at this point.
• Compare your two results using the percent difference formula.
• You will submit your saved file and your graph paper.

Assessment

​

Assessment 2: Demonstrate your skills

​

• In a new file, create a spreadsheet calculator for one of the following:
• The area and circumference of a circle, given a radius
• The area and perimeter of a rectangle, given four vertices
• The quadratic formula, given a quadratic equation in standard form
• Make sure your spreadsheet calculator is user friendly. Identify the function of the calculator. Make sure it is clear where users should plug in their initial values, and where they will find the answer.
• Test out your calculator to verify that it works as expected.
• Submit your new calculator when you are satisfied with your product.

Assessment

​

Differentiation

Remediation

This task can be simplified by providing a template file for the inputs and formula beforehand.

Extension/Enrichment

Students already comfortable with spreadsheets may be ready for the following spreadsheet skills:

• Rounding numbers
• Wrapping text
• Cell/text formatting and visual clues to make spreadsheets easy to understand for a user
• Slope formula
• Graphing the two points
• Writing a linear equation given two points (requires slope and calculation of y-intercept)

​

Examples of student work