GM Lesson 057 Technology for Linear and Non-linear Relationships

Learning Intentions

By the end of this lesson, students will be able to:

• Use spreadsheet formulas to evaluate linear and simple non-linear relationships.
• Display repeated formula calculations efficiently.
• Compare outputs from different input values.

Prerequisites

Students should already be able to:

• Substitute values into linear expressions.
• Substitute values into simple non-linear expressions involving powers.
• Evaluate formulae using correct order of operations.
• Construct tables of values from a formula.
• Interpret the meaning of input and output values in context.

Key Idea Summary

A spreadsheet can be used to evaluate a formula repeatedly without rewriting the calculation each time.

A linear relationship has a constant rate of change. It can often be written in the form:

where:

• is the input.
• is the output.
• is the rate of change.
• is the starting value.

A simple non-linear relationship does not have a constant rate of change. It may include powers such as:

or

In a spreadsheet:

• Each row can represent a different input value.
• A formula can be entered once and filled down.
• Outputs can be compared quickly.
• Changing one input value updates the connected output.

Direct Instruction and Worked Examples

Time Allocation

Time Allocation

• Introduction, warmup and vocabulary: 5 minutes
• Direct instruction: 15 minutes
• Understanding checks: 5 minutes
• Exercises: 20 minutes
• Homework: 20 to 30 minutes outside the lesson it was taught in.

Link to original

Spreadsheet Structure

In a spreadsheet, values are stored in cells.

For example:

Cell	Meaning
A2	first input value
B2	first output value
=2*A2+5	formula using the value in A2
fill down	copy the formula to later rows

If the formula is:

and the input is stored in cell A2, then the spreadsheet formula for is:

=2*A2+5

Worked Example 1: Linear Relationship in a Spreadsheet

A delivery company charges a fixed booking fee of $ plus $ per kilometre travelled.

Let:

• be the distance travelled in kilometres.
• be the total cost in dollars.

The relationship is:

Create a spreadsheet table for .

Distance km	Spreadsheet formula	Cost
	=3*A2+6	$
	=3*A3+6	$
	=3*A4+6	$
	=3*A5+6	$
	=3*A6+6	$
	=3*A7+6	$

The output increases by $ whenever the input increases by km.

Since the cost increases by a constant amount for equal increases in distance, this is a linear relationship.

Worked Example 2: Filling Down a Linear Formula

A phone plan costs $ per month plus $ per text message.

Let:

• be the number of text messages.
• be the monthly cost in dollars.

The formula is:

A spreadsheet is set up as follows:

Cell	Entry
A1	Number of texts
B1	Monthly cost
A2
A3
A4
A5
B2	=25+0.15*A2

The formula in B2 can be filled down to calculate the other costs.

Number of texts	Cost
	$
	$
	$
	$

The spreadsheet allows the same formula to be applied repeatedly without entering each calculation separately.

Worked Example 3: Simple Non-linear Relationship

The area of a square is given by:

where:

• is the side length.
• is the area.

Suppose the side lengths are cm, cm, cm, cm and cm.

If the side length is in cell A2, the spreadsheet formula for area is:

=A2^2

Side length cm	Spreadsheet formula	Area cm
	=A2^2
	=A3^2
	=A4^2
	=A5^2
	=A6^2

The outputs do not increase by a constant amount:

Since the first differences are not constant, this is a non-linear relationship.

Worked Example 4: Comparing Linear and Non-linear Outputs

Two formulas are being compared:

and

Create a table for .

	Linear formula	Non-linear formula

Spreadsheet formulas:

Column	Formula
Linear output	=4*A2+2
Non-linear output	=A2^2+2

For small values of , the linear formula gives larger outputs.

At , both formulas give the same output:

At , the non-linear formula gives the larger output.

This shows that spreadsheet tables can help compare different relationships efficiently.

Worked Example 5: Practical Non-linear Formula

The volume of a rectangular prism is:

A storage box has:

• width cm
• height cm
• variable length

So:

Although this simplified formula is linear in , the original formula has several variables. A spreadsheet can still calculate repeated outputs efficiently.

If:

• length is in column A
• width is in column B
• height is in column C

then the volume formula in column D is:

=A2*B2*C2

Length cm	Width cm	Height cm	Volume cm

Technology is useful because changing any one of the input values automatically changes the output.

Understanding Checks

Check 1

A formula is entered into a spreadsheet as:

=5*A2+12

What algebraic formula does this represent if the input is and the output is ?

Check 2

A formula is:

What does the represent in a practical cost situation?

Check 3

A spreadsheet uses the formula:

=A2^2+3

Calculate the output when the input value in A2 is .

Check 4

A table has outputs:

Is this likely to represent a linear or non-linear relationship? Explain.

Check 5

A table has outputs:

Is this likely to represent a linear or non-linear relationship? Explain.

Check 6

Explain why it is more efficient to fill down a spreadsheet formula than to type each calculation separately.

Exercises

Simple Familiar Exercises

Exercise 1

The formula is:

Complete the table.

Write the spreadsheet formula that could be entered in B2 if is in A2.

Exercise 2

The formula is:

Complete the table.

Write the spreadsheet formula that could be entered in B2 if is in A2.

Exercise 3

A gym charges a joining fee of $ plus $ per week.

Let be the number of weeks and be the total cost.

The formula is:

Complete the table.

Weeks	Cost

Exercise 4

A square garden has side length metres.

The area is:

Complete the table.

Side length m	Area m

State whether this relationship is linear or non-linear.

Exercise 5

A spreadsheet uses this formula:

=7*A2-5

Find the output when:

Input in A2	Output

Complex Familiar Exercises

Exercise 6

A delivery service has two pricing options.

Option A:

Option B:

where is the delivery distance in kilometres and is the cost in dollars.

Complete the table.

Distance km	Option A cost	Option B cost

Use the table to decide which option is cheaper for short distances and which option is cheaper for longer distances.

Exercise 7

A ball is dropped from a height, and its approximate distance fallen after seconds is modelled by:

where is the distance in metres.

Complete the table.

Time seconds	Distance metres

Explain why this is a non-linear relationship.

Exercise 8

A rectangular garden has width metres and fixed length metres.

The area is:

Complete the table.

Width m	Area m

State whether this relationship is linear or non-linear, and explain using the outputs.

Exercise 9

A square and a rectangle are being compared.

Square area:

Rectangle area:

Complete the table.

	Square area	Rectangle area

Use the table to identify when the square has a greater area than the rectangle.

Exercise 10

A spreadsheet contains the following columns:

Column	Meaning
A	number of items
B	cost per item
C	delivery fee
D	total cost

The total cost is calculated using:

where:

• is the number of items.
• is the price per item.
• is the delivery fee.

Write a spreadsheet formula for cell D2.

Then calculate the total cost for the following rows.

Number of items	Price per item	Delivery fee	Total cost
	$	$
	$	$
	$	$

Homework Problems

Problem 1

The formula is:

Complete the table.

Write the spreadsheet formula that could be used if is in cell A2.

Problem 2

The formula is:

Complete the table.

State whether the relationship is linear or non-linear.

Problem 3

A taxi company charges a flag fall of $ plus $ per kilometre.

Let be distance in kilometres and be total cost in dollars.

Write a formula for in terms of .

Complete the table.

Distance km	Cost

Problem 4

A cube has side length cm.

Its volume is:

Complete the table.

Side length cm	Volume cm

Explain why the relationship is non-linear.

Problem 5

Two rules are being compared:

and

Complete the table.

Use the table to decide when becomes greater than .