Excel II

CS 14A

Mar. 31, 1997

Several of these exercises require pre-existing Excel documents. These documents may be found on your R: drive, in nearly the same location in which the previous exercise's documents were found --- the final folder on the ``trail of folders'' is named excel2. Don't forget to do a Save As after you've opened each document.

Questions

These questions may appear on a forthcoming quiz. They come from the Excel Essentials readings)

1. Does Excel have a wrap text option?

2. What does ###### in a cell mean?

3. What is the fastest way of adjusting the width of a column to the column's contents?

4. What is a function argument?

5. What is the advantage of using a function to sum cells B2 through B9 rather than writing a formula like =B2+B3+...+B9?

6. Be familiar with the following functions from Table 4.1 in Project 4: average, date, max, min, pmt, round, sum, and time.

7. What should you do if you receive the error message #NAME? in a cell?

8. Be familiar with these logical operators used in the IF function: =, <, >, <=, >=.

Exercises

1. The file realty.xls contains a spreadsheet used to calculate the Asking Price and Selling Price on various real estate listings that were sold during June. It also contains the commission percentage paid to the agency as a result of selling those listings. Retrieve the file and follow the steps outlined:
   1. In cell E5, enter a formula to calculate the difference between the Asking Price and Selling Price. Copy the formula down the column.

   2. In cell F5, enter a formula to calculate the agency's commission (based on Selling Price). (Pay close attention to usage of relative and absolute cell references so that the formula copies correctly). Copy the formula down the column.

   3. In cell C13, enter a formula for total Asking Price. Copy this formula to columns D through F.

   4. Select the range C5:F13 and format the numbers so that they display with dollar signs and commas, but no decimal places (e.g., $450,000).

   5. Format cell B15 as a percentage.

   6. Format the label in cell A1 as a title, centered across the width of the spreadsheet. With the cell still selected, select cells A3:F4 as well and change the font to 12 point Arial bold italic.

   7. Select cells A4:F4 and create a bottom border to separate the headings from the data.

   8. Shade the commissions.

   9. Save your work.

2. You do excellent work, but somehow you never get noticed. All of your worksheets are completely accurate and meet or exceed the requirments imposed by your supervisor. Something is still lacking, however, and the helpme.xls workbook on the R: drive is typical of your work. A colleague took a look and said the problem is in formatting or the lack thereof. Let's see what you can do. E-mail the result to me as an attachment.

3. Let's explore the ``what if'' capabilities of a spreadsheet, as applied to an auto loan. Use the spreadsheet carloan.xls:
   1. Enter values into cells B1 through B3, B5, and B6.

   2. The monthly payment is only one expense; that is, a realistic estimate requires insurance, gas, and maintenance. Enter these labels in cells A8 through A10, then put projected expenses in cells B8 through B10.

   3. Add a Total label in cell A11 and a total formula in cell B11.

   4. Insert three rows at the top of the worksheet for the title: Can I afford it?. Put your name in the second row. Format the title, leaving the third row blank.

   5. In Cell A13, add the label: The Decision:. Enter an IF function in cell B13 that will display YES if the total expenses are $500 or less, and NO otherwise.

   6. Save your work. Change your initial values and observe how the decision is affected.