Problem 3 - Export to Excel - (25 extra credit points)

Learn to export Access data to Excel and manipulate the data using Excel formulas.This if for learning the export function and practice with Excel formulas and layout - no need to submit but you can earn 25 points of extra credit if you want to complete all the instructions.

Open Access. Open your Real Estate Database. In MS Office 2007 you can export a Query, a Table or a Form, but not a Report; the formatting that you did for the reports will be an option you can choose in the export process. Highlight the entire Table using the Object bar. Choose the External Data menu Tab and click on the Export to Excel spreadsheet button.

In the dialog box, determine:

• where you want the file saved and the file name
• choose the Save as type: to Microsoft Excel 97-2003 or 2007.
• Check the export data with formatting and layout
• Click on the OK button to Export.

Open your file in Excel. Insert some blank rows for your data section.

Instructions

1. In your "data section" ( upper part of your spreadsheet), enter the following:
   1. Your name.
   2. Today's date, =NOW(). This has a pair parentheses following =NOW. When this spreadsheet is opened, it will become the current date.
   3. Commission Rate = 6%
   4. Broker's Rate = 2.5% (deducted from the Commission Rate)
2. In your table section, do the following:
   1. Calculate how many days the firm has had the listing (current date - listing date). Use the =NOW() function for the current date. May need to Format the Date Column as "Date"; Format the Calculation as "Number"
   2. Calculate the selling price/square foot.
   3. Calculate the amount of commission earned if the house is sold at the selling price. use absolute cell references
   4. Calculate the broker's fees if the house is sold at the selling prices. use absolute cell references
   5. Calculate the total commissions to be earned at listed selling price.
   6. Calculate the total broker's fees to be earned at listed selling price.
   7. Sort based on City, in ascending order.
   8. Calculate the average/largest/smallest house based on square footage, average/oldest/newest age, average/highest/lowest selling price.
   9. Calculate the same statistics in number 8 for Santa Cruz homes only.
   10. Calculate the same statistics in number 8 for Watsonville homes only.
   11. Calculate the same statistics in number 8 for all other homes (other than Santa Cruz and Watsonville).
   12. Delete the two unused sheets in the workbook.
   13. Save and submit via the assignment tool in Blackboard.