Explore BrainMass

Explore BrainMass

    Pivot table and "IF" function

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    Please see attachment for data.

    I am attempting to write the "IF" function (step 7) and the pivot table and cluster column pivot chart (step 6) but I am having issues, please advise.

    Excel Exercises
    Spring 2013
    1) Open the Rock Island workbook located in Blackboard under Assignment folder.
    2) Enter your name and date on the documentation sheet
    3) In the Home Sales Data worksheet create an excel table in the range A6:K123. Name the table SalesData
    4) Add a calculated column named Days on Market which is the difference between the date sold and the date listed.
    5) Use conditional formatting to highlight records where the sales price was above the asking price. Make a copy of the home Sale data worksheet and rename it worksheet q5.
    6) Create a pivot table ("Insert" pivot table) to show the number of homes sold, average, highest, and lowest sale price for each of the three types of homes: Condo, Ranch and Victorian. Create a clustered column pivot chart for the average, maximum, and minimum sale prices. Name the sheet q7
    7) Create a new column in the home sale spreadsheet. Name the column Review. Use an IF function to insert the word "Review" in rows that satisfy the following condition :
    "On the market, more than 150 days and 25 or more years old"
    8) Extra Credit: use count if, sumif, and averageif to show the number of homes, total sales and average sales for each type of home. Save the result in a separate sheet "extra credit"

    © BrainMass Inc. brainmass.com October 10, 2019, 6:01 am ad1c9bdddf


    Solution Preview

    Please see the attached spreadsheet for all completed steps 1-8.

    In particular, for #6:
    I used a pivot table where I placed "Style" in the Row Labels quadrant so that we could get results by the type of home. In the Values ...

    Solution Summary

    The pivot tables and "IF" functions are examined.