EGN 100 Excel Spreadsheet Tutorial 1

Part I: Spreadsheet Principles and Techniques

Uses of Spreadsheets

Complex, repetitive calculations

Solve formulas

Answer “What if?” questions

Examples 2+2; x=v*t

Cells

Identified by a letter and a number

1.  In A1: =2 +7 Note formula bar

In A2: =11

In A3: =a1+a2

In A4 =sum(a1:a3)

Change the 11 in A2 to 7 and see how the spreadsheet changes – a great advantage of spreadsheets!

Note that the cells show the results of the formulas not the formulas themselves. We can see the formulas in the formula bar by selecting the cell.

2.  In A6, average what is in cells A1 through A3. Find the function for average by clicking on fx. Average(a1:a3). Limit the number of decimal figures to 1. Select the cell then Format-Cells-Number-Decimal Places 1-OK.

3.  Practice: Enter the following six numbers in B1 to B6: 226, 324, 285, 357, 272, 301

Sum them in B7.

Average them in B8. Limit the number of decimal figures to 0.

Consult with your neighbors to be sure you have done it correctly.

4. Create a running sum in column C. (A running sum is a sum of all the numbers as you go along: the first, the sum of the first and the second, the sum of the first, the second and the third, etc.) Type 226 (or =b1) in C1. Type =c1+b2 in C2. Copy C2 down to C6 by selecting C2, then placing the cursor on the little black square at the lower right of the cell, and drag down to C6. Check the final sum and note that a running sum has been created. Note that the final sum is the same as the sum of all the numbers. One at a time select each of the cells C1 to C6 and examine the code in each cell. Notice that the formulas have been adjusted so that the value in the cell immediately above (the previous sum) is added to the value in the cell to the immediate left (the number to be added to get the new sum).

5.  Create a row for column labels: Place the cursor on the 1 at the far left of row 1 and right click. Select Insert. Notice that an empty row of cells is inserted before the row selected. Type Data in the new B1 and Running Sum in C1.

6.  Center the column titles: Select cells B1 and C1 by left clicking on cell B1 and dragging to cell C1. Select Format at the top of the window, then select Cells-Alignment-Horizontal-Center-OK.

Widen Column C by placing the cursor on the dividing line between the letters C and D above Row 1 so that a double arrow appears, then drag to the right until the column is wide enough so that Running Sum is entirely in column C.

7.  Type 3 in D1. Multiply each data point in B2 to B7 by the constant in D1, placing a dollar sign in front of the 1; in D2 type =d$1*b2 and copy D2 down through D7. Change the constant in D1 to 4. What happens to the products? Placing a $ before a row letter or column number in a formula says to always go to that location to get a value, i.e. don’t increment as the formula is copied down or across. Consult with your neighbors to be sure you have done it correctly.

Here is an example of repeatedly solving an algebra problem for different values of the independent variable.

For y = 2.3x-2, what is y for x= 0, 1, 2, 3, and 4?

Type the letter x in cell A12.

Type the letter y in cell B12.

Type 0 in A13, =a13+1 in A14 and copy A14 down to A17.

In B13, type =2.3*a13-2 (This says “Multiply what is in cell A13 by 2.3 and subtract 2.) Do you get the answer you expect for x=0 in the formula?

Select B13. Left click and drag the small black square down to B17. Examine the code in the cells B13 through B17. How does it change? What is it saying will be done when the code is executed? Does the spreadsheet accurately calculate y for various values of x?

Here is what should appear as the result of doing the above exercise.

A B

x / y
0 / -2
1 / 0.3
2 / 2.6
3 / 4.9
4 / 7.2

Exercises

1.  What is y=3x+4 for x = 0, 2, 4, 6, and 8?

2.  Type m= in an available cell and b= in the cell just below it. Type the value of the constant m, i.e. 3, in the cell just right of that containing m=. Type the value of b, i.e. 4, in the cell just to the right of that containing b=. Write code that references those constants and calculates y = mx+b for x = 0, 2, 4, 6, and 8.

3.  What happens when you change the values of m and/or b?