Spreadsheet Calculations & Graphing

Projects

Diagram 2

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.