20.1 Calculations & Computer Modeling

• Spreadsheet for 20.1

Activity 20.1.1 – Using formulas

Examine the formulas shown in the boxes on the left of figure 20.3 and determine the spreadsheet representation of the formulas embedded in the shaded cells. Remember that the keys for addition, subtraction, multiplication, division, exponent, and scientific notation are +, -, *, /, ^, and E, respectively.

Activity 20.1.2 – Performing calculations

Figure 20.4 compares the speed of a variety of things expressed in miles/hour.  Download the spreadsheet from sciencesourcebook.com and convert the first entry (D3) to the metric equivalent (kilometers/hour) using the formula =C3*1.61 (there are 1.61 kilometers per mile), then copy this formula (in a relative manner) down the column.   The new contents of D4 should read =C4*1.61.  In most spreadsheet programs, a formula can be copied by dragging the cell handle (figure 20.1A) in the lower right corner of the source cell over the destination cells. Compare the speed of the fastest human relative to each item in the list by dividing the speed of the human (35.4 km/h) by the speed of the object.  For example, the formula in E3 should read 35.4/D3. Copy this formula in a relative manner throughout the column. 

(1) How many times faster is the fastest human than the average snail? 

(2) What fraction of the speed of sound can the fastest man run?

Activity 20.1.3 – Making a conversion tool

Virtually all scientists use the metric system when performing measurements and calculations.  Unfortunately, many Americans cannot relate to meters and liters because they have grown up using customary units such as feet and quarts.   In this activity you will make a conversion spreadsheet that will convert metric units to customary units.  Construct a spreadsheet in which one can enter a volume in liters and receive a volume measured in gallons, pecks, pints (liquid), and quarts (liquid). Refer to table 20.2 for conversion factors. Construct a second conversion chart in which one enters a distance in meters, and receives measurements in feet, miles and yards.

Activity 20.1.4 – Computer modeling of greenhouse gas emissions

One of the most powerful uses of a “number-cruncher” (spreadsheet) is to answer the question “What if…?”  The spreadsheet allows the user to produce models and predict outcomes.  Ecologists use spreadsheets to make predictions concerning the influence of various chemicals on global warming.  Figure 20.5[i] lists the global warming potentials (GWP) of the most common “greenhouse gases”.  The global warming potential is a measure of the estimated global warming contribution due to emission of a kilogram of  the gas compared to the emission of a kilogram of carbon dioxide.  Note that the other gases listed have GWPs substantially greater than carbon dioxide. Suppose Company-X releases 34 kilograms of  Freon, 15 kilograms of nitrous oxide, and 1 kilogram of sulfur hexafluoride, while Company-Y releases 450 kilograms of carbon dioxide, and 120 kilograms of CFC-12.  Which company would contribute more to global warming?  Answer this question by completing the spreadsheet shown figure 20.5 with the appropriate formulas.