Excel Introduction Exercise:

Complete all these steps in a new workbook and upload the workbook to moodle's Excel Introduction Exercise.

You are welcome to post to the General Course Discussion Questions Board. Remember that answering other people's questions earns you participation credit.

Optional help: If you do not already know how to do everything in this assignment, you can watch the movie showing the exercise. Click here for a movie to show you how to do this entire spreadsheet.

If you cannot figure out a step, do try the movie.

1. Create a new spreadsheet.

2. Use your spreadsheet as a simple calculator: (Goal: demonstrate that fomulas start with the equal sign.)
   

• Use your empty spreadsheet to calculate 9 times 57.5 (Remember to start with "=" sign to tell Excel it is a calculation coming. Operators are * / + - and ^ is for exponents)
• calculate 8 minus 7 and then multiply that result by 5 (You should get the result 5. If you get -27, you forgot parentheses.)
• divide 16 by 4.5 (Use / to divide. You should get the result 3.555555556.)

3. Use Excel to sum numbers and draw a border above the sum: (Goal: Demonstrate that each cell can control all 4 borders separately. Also demonstrate use of sum, one of many Excel functions.)

• In cell E2, enter 50001 (Just enter these numbers directly, with no = as they are not part of a formula)
• In cell E3, enter 532
• In cell E4, enter 566
• In cell E5, enter 600
• Change the border on the bottom of E5 to a double bar in preparation for a sum in E6. (Right click / Format / cell / border / click the double line and click the bottom of the box)
• In cell E6, calculate the sum of E2 through E5 using the sum function. (You can put your cursor in cell E6 and then click the sum button, or start with =sum( .) Notice: the sum formula starts with an equal sign and has a : between the ranges.

4. Use Excel to multiply numbers and then copy your formula: (Goal: Make formulas that reference other cells, so that when one cell changes, others will change. Also demonstrate that copying a formula will adjust the referenced cells to match the copy pattern.)

• In cell E7, multiply E2 by E3 by E4. (The result will be too large for the cell and show #####, so make the column bigger to see the number.)
• Copy cell E7 to F7 (You might see the error message - An error is fine to see because Excel will try to multiply F2, F3 & F4. ) Notice: when you copied one cell to the right, the cells referenced inside the formula also moved one cell to the right.
• Copy column E to column F and see that F7 is now good because its formula uses the numbers in F2, F3 & F4. . (Click on the letter E at the top and right click to copy. Click on the column F and right click to paste.)
• Change the number in F3 to 15 and see F7 change because the sum is calculated.
• In cell G2, create a formula that adds the values of E2 and F2. (Click on G2 and type =; Then click on E2 and type + and then click on F2 and press Enter.)
• Copy G2 to G3 , G4 and G5. See that the formulas relate to the new rows without you changing anything. (right click on G2 and choose copy. highlight G3-G5, right click and paste)

5. Setup an if statement based upon the values in column G. (Goal: Demonstrate use of a complex formula: Show that an If statement will cause one value to displayed when another value in the sheet meets a certain condition. Changing the value being tested will change the value in the cell. Also demonstrate column insertion and formula adjustment as a result.)

• Create a formula in H2 that displays "high" when a G2 is greater than 1300 and "low" when it is not greater than 1300. (With your cursor on H2, choose Formula / Insert Function or Insert / function on older versions and choose IF and press enter. Click inside the first box (logical test) and then click G2 and type > 1300. In the true box write "high" and in the other box write "low".)
• Change the value of G2 to see H2 switch from high to low. (Change E2 to 1 and F2 to 1 and see low and then F2 to 2000 and see high.) Notice: a formula can cause one cell to contain two different potential values.
• Copy the formula in H2 down. See the formula applies to the new row. (Right click copy H2 and then highlight H3-5 and right click paste). Notice: Earlier, you copied across and found your cell references inside the formula moved across. Now, you just copied down, and found the cell references moved down. The cell references move in the same pattern as the formula that was copied.
• Insert a column before G and see that your formulas in G and H adjusted. (Demonstrating how formulas adjust when columns insert. ) (Put your cursor on the "G' and right click and then choose insert column. Click on the IF formula and see that the cell reference automatically changed).

6. Make a formula use absolute reference and copy it. (Goal: Demonstrate that a formula can be forced to copy without changing its references)

• Copy cell E7's formula to cell F9. ( Right click E7 and click copy and then right click F9 and click paste )
• Change cell E7's formula to absolute addresses (by typing"$" before the actual row and column of each cell address ex: $E$2), and copy it to F9. ( The values in E7 and F9 should be the same. If they are not, you are welcome to just watch the end of the assignment video: Click here for a movie) Notice: When you used the $ before the column, it told excel to stay with E column even though you were copying to F column. The $ before the row told Excel to keep the same row number, even though you were copying down 2 rows.)
  

What to do if you cannot get this spreadsheet to be correct: You can still get full credit when this is wrong if you do the following:

Post a good question to the Excel Introduction Discussion Board about what is wrong.

The question must be well written in order to qualify. (A well written question gives the assignment name, step number, a description of your attempt and the result, an explanation of why you think it is wrong and a clear question you want answered..)

Write a note at the bottom of your spreadsheet telling me to look for the post.