Department of Biosystems and Agricultural Engineering                           Phone: (405) 744-4337

Oklahoma State University                                                                         Fax:      (405) 744-6059

Stillwater, OK 74078                                                     EMAIL: mstone@master.ceat.okstate.edu

MEMORANDA

Date:                September 26, 2005

To:                   BAE 1012

From:               Marvin Stone, Biosystems and Agricultural Engineering

Subject:            Homework Assignment #3

Create a spreadsheet using Excel™ that may be used to determine the size of a container that will be minimum weight for a particular volume.  You must create the spreadsheet in exactly the form shown on the web at http://biosystems.okstate.edu/home/mstone/1012/Downloads/1012hmwk3-05.xls.  Your spreadsheet must include the following features:

Show and allow the user to change the container volume, material thickness, and material density.

Show the tank wall thickness in fractions of 1/100^th inch and allow user to change the thickness.

Show the material density in pounds per cubic foot and allow the user to change the value.

Display the optimum height and diameter of the container in feet.

Labels for all rows, source data, and results must include units with proper superscripts as shown in the example.  Borders and colors should be formatted as in the example.

The following formulae will be useful:

· 1 cubic foot is equivalent to 1728 cubic inches.

· End Area = p D² / 4,   where D is diameter.

· h = Volume / End Area,  where h is height.

· Sidewall Area = hC,  where C is circumference (C = p D)

· Material Weight = Material Density times Material Volume (note, this is not tank volume).

In addition, the following spreadsheet functions will be useful:

MIN(B23:H23)  - Finds the minimum value in the range of cells B23 to H23

IF( EXP, First thing, Second thing )  - Checks the formula EXP to see if it is true, if so then it places the “first thing” in the cell, if not then it places the second thing in the cell.

This homework assignment is due before the end of the day, Friday Oct. 7, 2005 and should be submitted by e-mail to me on or before that time.  Be sure to identify your homework and email with your name!