Optimizing Areas and Perimeters Using a Spreadsheet


Consider the following problem:

A rectangular field, having 2700 yd^2, is to be enclosed by a fence, and an additional fence is to be used to divide the field down the middle. If the cost of the fence down the middle is $2 per running yard, and the fence along the sides costs $3 per running yard, find the dimensions of the field, to the nearest 5 yards, so that the cost of the fence will be the least.

To help you through the problem, there are several questions for you to answer. When you are asked to fill something in on your spreadsheet do so!!


1. The spreadsheet is set up for you. Just download the file field.xls and you're ready to go. Your first job is to fill the length column with multiples of 5, starting with 5. You'll have to figure out when to stop!! When you're done answer the following questions:

I stopped at the number ______, because _________________________________

__________________________________________________________________

 

2. a) Write an expression that represents the area of the pasture in terms of l (length) and w (width). A = _____________________

b) Solve the above equation for w. This equation will be used to find the dependent variable, w, in the spreadsheet. w = ______________________

Before you type the formula into the spreadsheet in cell, C7, write it down using the cell references. For the area cell reference you will need to use absolute references, because it will never change, so use $C$3. w = ____________________ Type this into the spreadsheet and fill down.

 

3. a) Write an equation that represents the cost of fencing the perimeter of the pasture in terms of l (length) and w (width). cost of perim = ______________________

b) Write an equation that represents the cost of putting the dividing fence in the pasture in terms l (length). cost of divider = _______________________

c) Write an equation that represents the total cost of putting up the fence in terms of l and w. total cost = ________________ Now you will need to write this formula using cell references. You only need to do it for the first cell in the first row, because you will "fill down" for the rest of the column. total cost = _____________________

 

4. Now that the spreadsheet is filled in you need to determine the dimensions of the field that will cost the least amount to fence and record them here (be sure to specify the length of the divider). __________________________

 

Extension: Find the dimensions of the field with an area of 3600 yd^2 so that the materials for the fence around the perimeter and the divider will cost the least.

 


On your own. . .You will need to set up your own spreadsheet to complete this problem.

 

problem #2: A Norman window consists of a rectangle surmounted by a semicircle. Find the shape of such a window that will admit the most light for a perimeter of 500 ft. Use 22/7 as an approximation for .

 

What are you trying to maximize????

1. Write an equation for the width of the window in terms of r (radius). w = ______________

 

2. a) Write an equation for the perimeter of the window in terms of r (radius), w (width) and l (length). p = _________________

 

b) Solve the above equation for l (length) in terms of w and r. l = _________________

 

3. a) Write an equation for the area of the semicircle part of the window in terms of r. area of semi. = ___________________

b) Write and equation for the area of the rectangular part of the window in terms of l and w. area of rectangle = ________________

c) Write an equation for the area of the whole window in terms of r, l and w. area of window = ________________

 

The formulas you have written above are all you will need to solve the problem, GOOD LUCK!!


More cool problems await on the next page. See you there!!