Excel Tool: Calculations with Data Sets

99783_6DataIntro_Tool.pdf

Excel Tool: Calculations with Data Sets

The best thing about Excel for the scientist is that it makes it very easy to work with data sets. In this assignment, we learn how to do basic calculations that involve values in a data set. Open a new Excel workbook, and perform the steps in this tutorial as we go. We will describe everything in this demonstration within the context of one example data set: x y

0.5 0.4

1.3 1.8

2 2.1

3 2.5

We begin by entering this data set into our Excel spreadsheet. (If you don't remember how to do this, please refer to the on-line "Getting Started with Microsoft Excel" tutorial, accessible from Dr. Caudill's teaching webpage.) Our spreadsheet now looks like this:

Basic Arithmetic:

Excel uses the usual computer symbols for addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^). As an illustration, let's suppose we want to compute a new column of numbers by adding

1 to each of the y-values in the data set. An easy way to do this in Excel is to first label

the column to the right of the y-column (i.e. the D-column) with "y+1". Then, we highlight cell D7, and type =C7+1 then press the "Enter" key. This formula says that "The value in cell D7 is obtained by taking the value in cell C7, and adding one to it." As seen in the image below, the resulting value for cell D7 is 0.4+1, or 1.4. Here is a very cool shortcut, which will allow us to perform the same calculation for each y-entry in our data set, without having to type in the above formula (appropriately edited) each time. We begin by highlighting the cell (D7, in this case) that contains the formula we want to extend to other data points. Notice that there is a little black square sitting in the southeast corner of the cell. Place the cursor over this little square. (The cursor will change from a white "block cross" to a thin black "stick cross".) Now, hold down the left mouse-button, and "drag" the little square down until the "highlighting frame" that was enclosing only cell D7 also encloses cells D8, D9, and D10. Now, let go of the mouse-button, and the spreadsheet should look like this: This "dragging" method is a very easy and useful way to apply a formula to every row of a data set, with minimum tediousness. This works because, when the formula is spread from one cell to another, the cell addresses included in the formula are automatically updated to use the correct row in the data table. To see this, double-click on cell D8, so that the formula associated to this cell will be visible: Note that, in this instance, the formula (=C7+1) that we originally entered into cell D7, has been "updated" to be =C8+1, to reflect the fact that we are now working in row 8, rather than in row 7. The same is true in cells D9 and D10.

Other Mathematical Operations.

As an example of Excel's mathematical functions, let's create another column to add to our data set. This column will consist of the square root of each of the y-values in the table. We begin, as before, by labeling the new column: In cell E7, we want to enter a formula that computes the square root of the value in cell C7. Excel has a built-in "square root" function, called SQRT. So, in cell E7, we enter the formula =SQRT(C7) and press the "Enter" key. You should see this: Now, "drag" the formula to the other cells (as we did previously) to complete the column: In this fashion, we can utilize many different mathematical functions (like we did here with the square root function) to compute various quantities from our data set. At the end of this document is a list of some of the most frequently-used mathematical formulas, and what they are called in Excel.

Average Rate of Change.

As a final illustration, we will compute one more column, the "average rate-of-change" (or "AROC") to add to our data set. This quantity is extremely important in many fields of science. For a data set, a separate AROC is computed for each consecutive pair of data points. In our example spreadsheet, we will compute the AROC values for our data table, by first labeling the next column: To compute AROC for a pair of data points, we simply view the two data points as coordinates of two points in the xy-plane, and compute the slope between these two points. (So, the AROC can be thought of as a slope, or as rise-over-run.) We enter this slope formula into cell F8 like this: =(C8-C7)/(B8-B7) then press "Enter". Here is the result: Now, drag the AROC formula to the other cells. (Note that we don't compute an AROC for cell F7. This is because AROC is computed using consecutive pairs of data points. In our data set, we only have three such pairs, we can only have three AROC values.)

Some Pre-Defined Excel Functions

ABS( ) - absolute value

SQRT( ) - square root

EXP( ) - base-e exponential function

LN( ) - natural logarithm (base e)

SIN( ) - sine function

COS( ) - cosine function

TAN( ) - tangent function

ATAN( ) - arctangent (i.e. inverse tangent) function (Similar for other trig functions) NOTE: For a complete list of Excel functions (mathematical, statistical, financial, and many more), click the "Insert" button along the top of the Excel window, then select "Function..." from the resulting drop-down menu.