"Students unanimously agreed that the use of a spreadsheet was helpful. They volunteered with the following three advantages: reduces arithmetic errors; allows the variable to be changed to see what effect they have; and the spatial layout of the numbers on the page, with cells and columns labeled, made the problem clearer. Good observations, I thought".
Here are the student handouts for this portion of the course:
-------------------------------------------------------------------- Overview of Basic Spreadsheet Operations Chem 121/122, Fall, 1994 ClarisWorks is an integrated multi-purpose program that contains a simple and easy-to-use spreadsheet. It is available on all of the Macintoshes in the WAM labs across campus. A. To launch the ClarisWorks spreadsheet program, click on the ClarisWorks button on the main menu screen, then click on the Spreadsheet button. B. To enter a label or a number into a cell, click on the cell, type, and press the enter key. C. To move to another cell, either click on the new cell or use the cursor (arrow) keys to move. D. To edit a cell, click on it, make the changes in the "entry box" at the top of the window, then press the enter key. E. To enter an equation into a cell, click on the cell, type an = sign followed by the desired equation, and press the enter key. When typing equations, use * for multiplication, / for division, + and - for addition and subtraction, ^ for exponents (e.g. ^3 means to raise to the third power). The values of other cells are referred to location (A1, B12, etc.). Use pi() for the value of pi. Example: if the radius of a sphere is contained in cell B7, then the equation for the volume of that sphere is "=(4/3)*pi()*B7^3". Don't forget to press enter when you are finished entering or editing an equation. F. You may optionally change the way a number is displayed if a cell by double-clicking on it. This brings up the dialog box shown in part here. Click on desired buttons and then click on the OK button to return to the spreadsheet. You may also use the Format pull-down menu to change the Font, Size, Style, Text Color, and Alignment of the contents of a cell, just like in a word processor. G. To save a spreadsheet on your floppy disk, select Save from the File pull-down menu, insert your floppy disk into the disk drive, type a file name, and press return. H. To print a spreadsheet, select Print... from the File pull-down menu and press return. You will need to have obtained print authorization I. To get more help, select ClarisWorks Help from the ? menu. --------------------------------------------------------------------------- Chemistry 121/122 Name______________________________________ Sept. 12, 1994 1. Spreadsheet Construction Exercise In each of the following spreadsheet layouts, marks the cells into which you are to type the numeric inputs (variables) and the blank cells are calculated cells that contain the equations referring to the input cells. Write your cell equations in the blank cells marked by the square brackets [ ]. A. Construct a simple spreadsheet that converts distances entered in kilometers into meters and centimeters. 1 kilometer = 1000 meters; 1 meter = 100 centimeters. Suggested layout: kilometers (km) [ ] meters (m) [ ] centimeters (cm) Use your spreadsheet to compute the radius of the earth (6300 km) in meters ____________ and in cm____________________? B. Construct a spreadsheet that performs the calculations needed to draw a scale drawing of the earth an its atmosphere, given the actual radius of the earth (6300 km), the thickness of the atmosphere (6 km) , and the radius in inches of the earth in the scale drawing. Suggested layout: Radius Thickness Kilometers [ ] Inches Use your spreadsheet to compute to compute the thickness, in inches, of the atmosphere in a scale drawing that has a radius of 5 inches. __________. C. Construct a spreadsheet that performs calculations related to car travel, taking the first four items in the table below as givens and computing the last three. Distance, miles Speed, miles/hour Mileage, miles/gallon Price of gas, $/gallon Time required, hours [ ] Fuel used, gallons [ ] Cost of trip, dollars [ ] If you drove 1000 miles at a steady 55 miles/hour, in a car that gets 20 miles/gallon, when gas costs $1.20 per gallon, how long would it take ___________, how many gallons of gas would you use ____________ , and how much would you spend on the gas ____________? D. Construct a spreadsheet equation that computes the number of molecules of an air pollutant per liter, given the concentration of the pollutant in parts per million (ppm). Any gas contains a total of 2 x 10^22 molecules per liter at atmospheric pressure. Given that the permissible concentration of sulfur dioxide according to the 1991 EPA standards is 0.03 ppm. How many molecules of sulfur dioxide would there be in one liter of air at this concentration? _________________ (Hint: 1,000,000 ppm = 100% = 2 x 10^22 molecules per liter). E. Construct a spreadsheet that computes the number of liters (1 liter = 1000 cm3) of air in a rectangular room of given height, width, and length in feet. (1 foot = 12 inches; 1 inch = 2.54 cm). Compute the volume of a 12' x 10' x 8' room ____________. Suggested layout: Length Width Height Volume Feet Inches [ ] [ ] [ ] Centimeters [ ] [ ] [ ] [ ] Cubic centimeters [ ] Liters F. Construct a spreadsheet that computes the volume in liters of the atmosphere of the earth, assuming that the earth is a sphere with a radius of 6300 km and that the atmosphere is 5 km thick. Suggested layout: Radius Thickness of Radius of earth Volume of of earth atmosphere + atmosphere atmosphere kilometers [ ] meters [ ] [ ] centimeters [ ] [ ] volume, cm3 [ ] [ ] [ ] volume, liters [ ] Suppose that you had used a value of 10 km for the thickness of the atmosphere, instead of 5 km. How would this effect the calculated volume of the atmosphere? G. Expand the above spreadsheet to solve the following problem. Suppose that one liter of a stable gas X is released into the outside air and completely mixed with the entire atmosphere of the earth. What would be the concentration in molecules per liter of X in the atmosphere after mixing? (Any gas contains a total of 2 x 10^22 molecules per liter at atmospheric pressure). 2. Plotting Spreadsheet Data 1.Create a spreadsheet containing the data from in Table 1.1 from the textbook. ----------------------------------------- Air composition ----------------------------------------- Inhaled (%) Exhaled (%) Nitrogen 78 75 Oxygen 21 16 Argon 0.9 0.9 Carbon dioxide 0.03 4 water 4 4 ----------------------------------------- To plot these data, click on the upper left cell and hold the mouse button down while dragging the mouse to the lower right cell of the table, then let up on the mouse button. The selected region of the spreadsheet is shown in black. Then select Make Chart... from the Options pull-down menu. Then click on the icon of the desired chart type and click on the OK button. The resulting graph can be moved by dragging the interior portion of the graph and re-sized by dragging the small black "handles" at the four corners. You can change to another chart type by double-clicking on the graph interior, or you can change the axes by double-clicking on them or the legend by double-clicking on it. Play around with the various graphing options. 2. Create a spreadsheet containing the data from in Table 1.2 from the textbook (EPA data on major air pollutant concentrations in US cities). Plot these data in a way that attempts to compare the air pollution levels of these cities. Note the difficulty caused by the large numerical range of the data. The permissible limits for the four pollutants listed are 9, 0.12, 0.03, and 0.053 ppm, respectively. Modify the above spreadsheet to plot the relative extent to which each pollution concentration exceeds the permissible limit for that pollutant. In the space below describe how you accomplished that task.