Excel Lab 8-31

Use COUNTIFS and AVERAGEIFS. Identify structured references. Sort and filter records.

As part of its cooperation with the WorldWide Campaign, AllAround Vision Care has found that some patients are flown in from other locations and require overnight accommodations. With help from a major hospital, it has assembled information about homes that might be purchased as recovery or respite locations for patients and/or their families. You are to prepare the summary information for the data.

• Open Excel_LA8-31 start file and save it as your initials8-31.
• Click cell B3 on the Summary sheet. Use COUNTIFS with a structured reference to determine how many homes listed on the Properties sheet meet the Bedrooms and Baths criteria specified in column A on the Summary sheet. Use Table1 data range already set for the data table on the Properties sheet. Copy the formula and edit the criteria for each row. HINT: =COUNTIFS(Table1[Bedrooms],"=5",Table1[Baths],">=4")
• Click cell C3 on the Summary sheet. Use AVERAGEIFS with a structured reference to calculate an average Last Sale Price for the number of Bedrooms and Baths home criteria specified in column A on the Summary sheet. Use Table1 data range already set for the data table on the Properties sheet. (Refer to the Hint formulaCopy the formula and edit the criteria for each row. Format the results as Currency style from the drop-down menu.
• On the Properties sheet, sort the records in descending order by last sales price, and filter them to show only those with solar heat.
• Make a copy of the Summary sheet and place it at the end. Display the formulas. Set landscape orientation and turn off horizontal centering. Set the sheet to fit to one page.
• Prepare and submit your work.