Dave Heppenstall |  Contact

Lab 3 4 5 6 7 8 9 10 |  » 

Spreadsheets II

7 February 2008

This tuitorial is an update to the official Lab Five web page. It is also an update to the following official supplemental pages: Data Analysis and Presentation, Spreadsheets II and Spreadsheets III.

On This Page:

• Virus Scan your USB Drive
• Download Necessary Files from the FTP Server
• Average, Count and Sum Functions
• Making a Graph
• Linking Excel Data into Word
• Absolute Cell Addressing in Excel
• Linking Data Between Spreadsheets
• Goal Seeking
• Scenarios
• Auditing

Virus Scan your USB Drive

First things first: Go into the My Computer window and right-click on your USB Drive. Select Scan for viruses... as follows:

This is a good habit to get into every time you plug your USB drive into a lab computer. At least, the system administrator(s) would love it if this was a habit of yours.

Download Necessary Files from the FTP Server

Ok, head on over to our FTP server and grab the following files: lab3.xls, lab2.xls, lab2_A.xls and Goal Seek-Example.xls.

We will need these files for today's lab. Store them somewhere on your USB drive.

Average, Count and Sum Functions

Open lab3.xls.

We will review how to enter the sum function as well as introduce the count and average functions. First, calculate the sum for the columns B through G as in the previous lab:

Now lets try exactly the same thing, except with the rows 3 through 5 and with the average function. Instead of typing "sum", type "average".

Finally, use the count function. Just pick any cell, type "count(", select any cells, type ")" and hit enter. The result of this function is simply the count of how many cells you selected.

Making a Graph

Open lab2.xls.

We will create a chart using the data contained within this spreadhseet. First, select cells A3 through B5; this is all the sales raw data for the store. We want to visually determine the proportion of each sales item - a Pie Chart is perfect for this. :)

Go to the Insert menu tab and choose Pie. Let's pick the first 2-D Pie Chart:

Now we can clearly see that Clothes is by far the item responsable for the most income. Lets change the chart type to a Bar Graph instead. Right-click on the empty, white area of the chart box and select Change Chart Type.

The following dialog box will appear. Select the Bar option as shown:

Now we have a Bar Graph instead of a Pie Chart! We didn't need to re-select data.

Linking Excel Data into Word

Open Microsoft Word.

What we will do now is transfer our new chart into a Word document. We want the data to remain linked to the Excel spreadhseet such that if we make changes to the spreadhseet, the chart will reflect the changes in Word.

Click on an empty area of the Chart in Excel and copy it to the clipboard (CTRL C). Switch focus over to Word and paste in the contents of the clipboard (CTRL V) somewhere into the document. The chart will appear in Word. Look for the little clipboard icon to the bottom-right of this chart and click on it:

Make sure that Chart (linked to Excel data) is selected and enabled as above.

Go back to Excel and make some changes to the data in the spreadhseet. You'll notice that your Excel chart will be automatically updated. Switch over to Word and you'll find that the Chart has been automatically updated there as well!

Repeat this process for the actual spreadhseet data in Excel.

Absolute Cell Addressing in Excel

Open lab2_A.xls.

Copy the contents of cell C9 into cell D9. The result should look like this:

Notice how the pasted version is different from the original cell. Why? The sum function in the C9 function looked like this: =sum(C7:C9), which uses relative addressing. The function in the destination cell has changed to: =sum(D7:D9). That is, when the function was pasted, the referenced cells were modified relative to its new location within the spreadhseet.

Now, copy the contents of cell I9 into J9. The result should look like this:

This time, the contents of the cell did not change. What happened? The sum function in the I9 function looked like this: =sum($I$7:$I$9), which uses absolute addressing; the dollar signs essentially mean, "never change this value, even if I am moved around." The pasted function has not changed at all.

Lets try to exercise this feature ourselves now. Copy the contents of cell C17 into cells C18 and C19. The formula from the original cell has been adjusted relative to its new position within the spreadsheet.

In cell B18, we're going to try and duplicate the formula from B17 except adjust it to use absolute addressing.

Ok, it looks a little strange. What did I do? All I did was place a dollar sign before every letter and before every number. If you miss one, that value will be addressed relatively.

Finally, copy and paste our new formula from B18 into B19 and B20. The result should look like this:

All you need to remember is: the dollar sign makes it an absolute address!

Linking Data Between Spreadsheets

We should already have lab2_A.xls and lab2.xls open. We will be using these files concurrently; size and move your windows around so that you can see them simultaneously on your screen.

In cell A1 of lab2_A.xls, hit the equal sign "=". Without pressing any more keys (yet), click on cell B5 of lab2.xls. (You may need to click more than once because the window needs to first get focus.) Now hit the Enter key. The formula in the original cell now looks like this:

While we already know that the dollar signs mean absolute addressing, the addition of the words before the exclamation mark indicates in which sheet of which file the formula is referring to.

The formula in file lab2_A.xls now references data in lab2.xls.

Goal Seeking

Switch back to lab2.xls.

What we're going to do now is have Excel create a certain condition by meeting another condition. First, add a sum function in cell B6 to indicate the sales total.

Open the Data tab menu, click What if Analysis and select Goal Seek as shown:

In the window which appears, enter cell B5 (the Clothes revenue cell) with a value of 5000 by changing cell B5. That is, determine what change in clothing value will yield a new total of 5000.

Our spreadsheet will automatically modify itself as follows:

Scenarios

We will still use lab2.xls for this subsection.

Add a sum function to cell B13 for total expenses. Finally, in cell B15, enter "=B6-B13" to give us the total profit. What would happen if book sales went to zero? What would happen if rent tripled? Likly scenarios one might consider.

Open the Data tab menu, click What if Analysis and select Goal Seek as shown:

The following dialog will appear. Click Add.

Enter data for our first scenario into the window which appears as follows. Repeat for the other scenario.

In the main scenario window, click Summary. The following window will appear. Enter B15 as the result cell (our profit/loss cell).

Our scenarios are detailed in the table which appears:

Auditing

Select any cell which contains a formula. For this example, select cell C9 in lab2_A.xls. Open the Format tab menu and click Trace Precedents:

The backwards relationships between this cell and its precedents are now visually represented on the spreadsheet.

This representation will assist you in determining how the forumulas in your spreadhseet are interdependant.

Lab 3 4 5 6 7 8 9 10 |  »