On the Home Sales Data worksheet, in column E, calculate the Tax Percentage. Using a nested IF calculate the following (Hint: do not use the function AND because if the house does not have 1 bedroom and does not have 2 bedrooms then the house MUST have more than 3 bedrooms.)
a. 1 Bedroom homes use 0.75%
b. 2 Bedroom homes use 1%
c. For all homes with 3 or more bedrooms:
i. if the age of the home is less than 10 years old, use 1.25%
ii. If the age of the home is less than 20 years old (but more than 9), use 1.5%
iii. If the age of the home is 20 years old or more (or for all other homes), use 1.75%
3. In Column F, multiply the Tax Percentage by the Sales Price
4. In Column M, calculate the Realty Company from the lookup table in cells P3:Q7. In cell P3, change the Realty Name to your last name Realty (i.e. Davis Realty). Use the VLOOKUP command to calculate. (Hint: explore the fourth component of the VLOOKUP function.) So, home with square footage from:
a. 0 to less than 1200 feet, go to (Your Name) Realty
b. 1200 to less than 1800 feet, go to Elmo Homes
c. 1800 to less than 2500 feet, go to Borat Homes
d. 2500 to less than 3000 feet, go to Soprano Builders
e. Homes with 3000 feet or more, go to Sunshine Developers
5. Print this worksheet with the formulas displayed in landscape mode. Be sure to adjust the column widths to show the entire formula but remove excess space. Select ONLY the House Listing table to print (Not the Lookup table.) In Page Setup, have Excel print it to fit to 1 page wide by 2 pages tall. The type should be very small but still readable.
6. Go back to displaying cell values, not formulas. Make cells A1:M118 a table named "Listing" in table style Light 10
a. Sort data by the following:
i. Realty Company in A to Z order
ii. Style in A to Z order
iii. Date Sold in Newest to Oldest
b. Add in Total Row and have it average Sales Price, average Square Footage, count Realty Company columns
c. Print this Listing table ONLY in landscape mode. In Page Setup, print the table to fit to 1 page wide by 2 pages tall. Your printout should contain only the Listing table including the Totals Row.
7. On the Summary worksheet, use the COUNTIF and SUMIF functions to fill in columns B and C. For example, in cell B12, count the number of homes with 1 Bedroom. In cell C12, sum the Taxes for homes with 1 Bedroom. Repeat in the cells below for the different numbers of Bedrooms. Making sure all formulas are fully displayed, print out this worksheet with formulas displayed in landscape mode. Fit this into a single page.
8. Create a PivotTable to a new worksheet named "Pivot" based on the data in the Home Sales Data worksheet. Use Style, Sales Price and Listing ID fields.
a. Style is a Row Label
b. Sales Price should calculate as an average
c. Listing ID should calculate as a count
d. Print this Pivot Table in portrait mode.
9. Copy Home Sales Data worksheet to a new worksheet and name the new worksheet "Subtotals".
a. Add subtotals by Realty Company and calculate the average of the Asking Price
b. Print Spreadsheet as a Level 2 Outline in Landscape mode. Fit to one page.
10. Put your worksheets in the following order:
a. Summary
b. Home Sales Data
c. Pivot
d. Subtotals
11. Reminder: don't forget to add your footer to ALL your printouts. Hand in the following to class by the due date and staple all your printouts together in this order PLEASE:
a. Printout of formulas from Summary worksheet
b. Printout of formulas of Home Sales Data worksheet
c. Printout of multiple criteria sort with Totals Row
d. Printout of Pivot Table
e. Printout of Subtotals