Algebraic Problem-Solving Using Spreadsheets:

Setting Up a Problem on a Spreadsheet

by Margaret Sinclair

Back to Algebraic Problem-Solving using Spreadsheets || Math Units: Contents

The aims of this section are:

• to have student's approach a problem from a numerical basis;
• to introduce the idea that a formula can be written in several ways and give the same answer. For example, we could write A+B for the sum of the first two columns, but if B were 2A, we could write A+2A instead.

Problem 1: Animals in the farmyard

There are 53 horses and cows in Farmer Wren's farmyard. There are 21 more cows than horses. How many of each type of animal are there?

Think about the Problem:

• the total number of cows and horses is 53
• there are more cows than horses
• there are 21 more cows than horses

Enter the Information

Once the information is entered, find the row that has 53 animals in the Total column. Look at columns A and B to discover how many cows and horses there are in Farmer Wren's farmyard. (You should have a row that shows 16 horses, 37 cows and a total of 53.)

If you haven't done so already, set your Web browser to use ClarisWorks as a helper application for these examples. Then open this spreadsheet to practice entering the formulas.

If you have Microsoft Excel, open this spreadsheet.

Experiment

In column D type 2*A2+21 and copy this formula down.

What do you notice?

Why do you think this happened?

Develop an Equation

The value in column C was found by adding the values in columns A and B so we can write: A+B=C

But column B is just column A plus 21. Therefore: A+(A+21)=C

Collecting terms gives: 2A+21=C

Since farmer Wren has 53 animals we know that 2A+21=53

Solving this equation we find that 2A=32

Therefore: A=16

Our solution is that Farmer Wren has 16 horses and 37 cows.

Problem 2: Theater Tickets

For a play, the ticket prices were \$5.00 per child and \$8.50 per adult. The children bought 100 more tickets than the adults. The total box office income was \$905.00.

Set up a spreadsheet to do the calculations and to work out how many tickets of each type were sold.

Think about the Problem

• some adults bought tickets that cost \$8.50 each
• some children bought tickets that cost \$5.00 each
• there were 100 more children than adults
• the total of all the ticket money was \$905.00

Enter the Information

Once the information is entered, find the row that has a total of \$905.00 in column C. Look at columns A and B to discover how many children and adult tickets were sold.

If you have ClarisWorks, open this spreadsheet to find the solution yourself.

If you have Microsoft Excel, open this spreadsheet.

Experiment

In column D type A2*8.50+(A2+100)*5 and copy this formula down.

What do you notice?

Why do you think this happened?

Develop an Equation

The value in column C was found by adding the value in column A multiplied by 8.50 and the value in column B multiplied by 5.00 so we can write: 8.5A+5B = C

But column B is just column A plus 100.

Therefore: 8.5A+5(A+100)=C

Collecting terms gives: 13.5A+500=C

Since the total value of the tickets sold was \$905.00: 13.5A+500=905

Solving this equation we find that: 13.5A=405

Therefore: A=30

Our solution is that 30 adult tickets were sold. This means that 130 children bought tickets.

[Graphing with Spreadsheets, from Suzanne Alejandre's math units.]

 1. Formulas on a Spreadsheet 3. Spreadsheet problems 2. Setting up a problem on a spreadsheet 4. Follow-up in the classroom

Suggestion Box || Home || The Collection || Help Desk || Quick Reference || Search