ClarisWorks: Solving Problems with Spreadsheets
"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.