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.
These questions may appear on a forthcoming quiz. They come from the
Excel Essentials readings)
- Does Excel have a wrap text option?
- What does
###### in a cell mean?
- What is the fastest way of adjusting the width of a column to the
- What is a function argument?
- What is the advantage of using a function to sum cells B2
through B9 rather than writing a formula like
- Be familiar with the following functions from Table 4.1 in Project 4:
average, date, max, min, pmt, round, sum, and time.
- What should you do if you receive the error message
- Be familiar with these logical operators used in the IF
- 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:
- In cell E5, enter a formula to calculate the difference
between the Asking Price and Selling Price. Copy the formula down the
- 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.
- In cell C13, enter a formula for total Asking Price. Copy
this formula to columns D through F.
- Select the range
C5:F13 and format the numbers so that they
display with dollar signs and commas, but no decimal places (e.g.,
- Format cell B15 as a percentage.
- 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.
- Select cells
A4:F4 and create a bottom border to separate
the headings from the data.
- Shade the commissions.
- Save your work.
- 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
- Let's explore the ``what if'' capabilities of a spreadsheet, as
applied to an auto loan. Use the spreadsheet carloan.xls:
- Enter values into cells B1 through B3, B5, and
- 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.
- Add a Total label in cell A11 and a total formula in cell
- 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.
- 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.
- Save your work. Change your initial values and observe how the
decision is affected.
Thomas P. Kelliher
Mon Mar 24 13:00:30 EST 1997