Calculations & Computer Modeling

This activity is from the Sourcebook for Teaching Science.

• FILE: Spreadsheet for Calculations

• Videos

An electronic spreadsheet, such as Microsoft Excel®, presents data in worksheets (figure 20.1A), documents that can store, manipulate, calculate, and analyze data. Data is placed in cells formed at the intersection of columns and rows. Cells can hold numbers, formulas, or labels. The address or reference of a cell is the combination of the column and row headings. For example, the address B5 refers to data found at the intersection of the column-B and row-5. Cells may contain formulas linked to the contents of other cells. For example, the formula =A5+B5 refers to the sum of the contents of cells A5 and B5. When the value of a cell is changed, all cells with formulas referring to that cell are updated. Note that the keys for addition, subtraction, multiplication, division, exponent, and scientific notation are +,-,*,/,^, and E, respectively.

Figure 20.2 is a spreadsheet that shows a variety of calculations common in science. The shaded boxes on the right side of the spreadsheet reveal the formulas that are embedded in the boxed cells immediately to their left. The temperature conversion equations convert Fahrenheit to Celsius (figure 20.2A). To convert, one must first subtract 32 °F, then multiply by 5/9. The formula in D3 reads =(B3-32)*5/9, where B3 represents the Fahrenheit temperature in cell B3, and * indicates multiplication. Note that the formulas in cells D4 and D5 parallel D3, referencing the temperatures in B4 and B5.

The formula for calculating the distance an object travels in freefall is d=1/2gt2 , where g is the acceleration due to gravity, and t is the elapsed time (figure 20.2B). The formula in D9 is =1/2*$B$9*(C9)^2. Since g (the acceleration due to gravity) does not change, the formulas for D10 (=1/2*$B$9*(C10)^2), and D11 (=1/2*$B$9*(C11)^2), both refer to B9 where the value of g is found. A dollar sign ($) in a cell reference makes it an absolute reference for the purpose of copying. If the formula is copied to another cell, $B$9 remains the same, while the other reference (C9) changes to C10 or C11 depending on the row to which it is copied. To calculate the acceleration due to gravity on the moon or another planet, one merely needs to substitute the applicable value for g in cell B9.

In figure 20.2C, the force of gravity on an object at the earth’s surface is calculated using Newton’s Universal Law of Gravitation. The equation in D15 reads =C15*(C16*C17)/C18^2/ where cell C15 contains the universal gravitational constant, G, C16 contains the mass of the object, C17 contains the mass of the Earth, C18 contains the radius of the earth, and the ^ character indicates an exponent. By changing the contents of C16, the mass of the object, one can determine the force (weight) of any object. Note that mass of the Earth (C16, in kg) and the radius of the earth (C17, in meters) are so large that they must be represented in scientific format with exponential notation. 5.97E+24 is the spreadsheet format for 5.97 x 1024 kg, the mass of the Earth, and 6.38E+06 is the spreadsheet equivalent of 6.38 x 106 m, the radius of the Earth.

Figure 20.3D illustrates some basic statistics. The values in B22-B28 represent experimental values for the volume of one mole of gas at standard temperature and pressure, measured in liters. The values in D22 to D26 represent the sum, maximum, minimum, average, and standard deviation of these volumes. In each case, the statistical function is applied over the range of experimental values (B22:B28). Changing values in cells B22 to B28 may result in a change in the average molar volume, as well as the associated statistics. Such statistics are frequently used in laboratory experiments.

In formula in figure 20.2E determines if a projectile going a given speed (the values listed in B32, B33, B34 or B35) will reach escape velocity and leave the Earth’s gravitational field. Escape velocity is 40,200 km/h. The formulas embedded in cells C32 to C35 are logic statements =IF(+B32>=40200,”yes”,”no”). If the contents of cell B32 (the velocity of the first craft) is greater than or equal to 40200 km/h, then it will escape the earth’s gravitational field and the formula returns the conclusion, “yes, it will escape”. If, however, the velocity is less than 40200 km/h, then it the formula returns the conclusion, “no, it will not escape.” The spreadsheets for this activity are available online at sciencesourcebook.com.

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.