Spreadsheet Calculations

Spreadsheet Basics

The activities in this chapter use basic mathematics operations. Note that the keys for addition, subtraction, multiplication, division, exponent, and scientific notation are +,-,*,/,^, and E, respectively (figure 20.1B). The order of operation (sequence in which calculations are performed within a cell) is algebraic (figure 20.1C).

Calculations are preceded by an equal sign (=) and functions operate on data enclosed in parentheses or brackets (the argument). For example, =COS(D5) returns the value for the cosine of the contents of cell D5. A range of variables is designated by a colon (:) between the beginning and ending cells. For example, =SUM(B4:B12) gives the sum of all values between B4 and B12. One can also specify a series of values by separating them with commas. For example, =SUM(B6,B9) delivers the sum of these two cells, while =SUM(B6,B7,B10:B12) delivers the sum of the two individual cells, B6 and B7, and the sum of the range from B10 to B12. Figure 20.1D lists the most frequently used functions in teaching science.

Spreadsheets allow formulas to be copied to adjacent cells. In general, formulas are copied relatively. For example, if the formula =B4/B13 is copied down a cell, it will be B5/B14. Similarly, if it is copied up a cell, it will have the value B3/B12. If you want part of the formula to refer to a specific cell so the reference does not change when copied, it is necessary to use an absolute reference, indicated by a dollar sign ($). For example, if the formula =B4/$B$13 is copied down a cell, the first value changes relatively, while the second remains the same, =B5/$B$13.

Although all spreadsheet programs have the same fundamental capabilities, they differ in mechanics, and as a result we provide only generic instructions. To plot data, the user must select relevant cells and the desired graph format (bar, line, X-Y, etc). Once a graph has been made, the user may redefine the source data (location of data series, x-values, y-values), titles (chart title, x-axis title, y-axis title), axes, gridlines, legends and data labels. In most programs, one can change a feature by selecting it (right or double-clicking on it) and choosing the options that accompany the contextual menu that appears.

In this chapter, students gain competence developing and interpreting the most common types of tables and graphs used in science (table 20.1). Tables are best when precision is required, while graphs are best when one needs to make quantitative comparisons, or see trends and relationships. However, if one want to show spatial relationships, it is better to use maps, and if one wants to show non-quantitative relationships, diagrams are best. Charts can be used for a wide range of purposes. Spreadsheet programs generate tables, graphs, and charts.

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.