Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
The spreadsheet contains codes (BA, DA, SA) to represent agent roles (Buyer's Agent, Dual Agent, Seller's Agent). You want to switch the codes for the actual descriptions. n cell E12 of the Details sheet, insert the SWITCH function to evaluate the agent code in cel 012. Include mixed cell references to the codes and roles in the range J2:K4 for the values and results arguments. use all cell references in the function. Copy the function to the range E13:E39.

The spreadsheet contains codes (BA, DA, SA) to represent agent roles (Buyer's Agent, Dual Agent, Seller's Agent). You want to switch the codes for the actual descriptions. n cell E12 of the Details sheet, insert the SWITCH function to evaluate the agent code in cel 012. Include mixed cell references to the codes and roles in the range J2:K4 for the values and results arguments. use all cell references in the function. Copy the function to the range E13:E39.

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question
The spreadsheet contains codes (BA, DA, SA) to represent agent roles (Buyer's Agent, Dual Agent, Seller's Agent). You want to switch the codes for the actual descriptions. In cell E12 of the Details sheet, insert the SWITCH function to evaluate the agent code in cell D12. Include mixed cell references to the codes and roles in the range J2:K4 for the values and results arguments. use all cell references in the function. Copy the function to the range E13:E39. Now you want to calculate the number of days between the list date and sale date. In cell J12, insert the DAYS function to calculate the number of days between the Listing Date and the Sale Date. Copy the function to the range J13:J39. You want to calculate agent commissions based on their role. In cell K12, insert the IFS function to calculate the agent's commission based on the agent code and the applicable rates in the range L2:L4. Use relative and mixed references correctly. Copy the function to the range K13:K39. You want to calculate a bonus if the sold price was at least equal to the listing price, and if the house sold within 30 days after being listed. In cell L12, insert an IF function with a nested AND function to calculate a bonus. The AND function should ensure both conditions are met: Sold Price divided by the Listing Price is greater than or equal to 100% (cellI L7) and the Days on Market are less than or equal to 30 (cell L8). If both conditions are met, the bonus is $1,000 (cell L9). Otherwise, the bonus is $0. Use mixed cell references to the input values in the range L7:L9. Copy the function to the range L12:L39. The top-left section of the spreadsheet is designed for summary statistics for one condition. You will calculate average selling prices and the number of houses sold in each city (the condition). In cell B2, insert the AVERAGEIF function to calculate the average Sold Price for houses in the city of Alpine. Use mixed references for the range; use a relative reference to cell A2. Copy the function and use the Paste Formulas option to paste the function in the range B3:B5 so that the bottom border in cell B5 is preserved.
Transcribed Image Text:The spreadsheet contains codes (BA, DA, SA) to represent agent roles (Buyer's Agent, Dual Agent, Seller's Agent). You want to switch the codes for the actual descriptions. In cell E12 of the Details sheet, insert the SWITCH function to evaluate the agent code in cell D12. Include mixed cell references to the codes and roles in the range J2:K4 for the values and results arguments. use all cell references in the function. Copy the function to the range E13:E39. Now you want to calculate the number of days between the list date and sale date. In cell J12, insert the DAYS function to calculate the number of days between the Listing Date and the Sale Date. Copy the function to the range J13:J39. You want to calculate agent commissions based on their role. In cell K12, insert the IFS function to calculate the agent's commission based on the agent code and the applicable rates in the range L2:L4. Use relative and mixed references correctly. Copy the function to the range K13:K39. You want to calculate a bonus if the sold price was at least equal to the listing price, and if the house sold within 30 days after being listed. In cell L12, insert an IF function with a nested AND function to calculate a bonus. The AND function should ensure both conditions are met: Sold Price divided by the Listing Price is greater than or equal to 100% (cellI L7) and the Days on Market are less than or equal to 30 (cell L8). If both conditions are met, the bonus is $1,000 (cell L9). Otherwise, the bonus is $0. Use mixed cell references to the input values in the range L7:L9. Copy the function to the range L12:L39. The top-left section of the spreadsheet is designed for summary statistics for one condition. You will calculate average selling prices and the number of houses sold in each city (the condition). In cell B2, insert the AVERAGEIF function to calculate the average Sold Price for houses in the city of Alpine. Use mixed references for the range; use a relative reference to cell A2. Copy the function and use the Paste Formulas option to paste the function in the range B3:B5 so that the bottom border in cell B5 is preserved.
Average Sold Total Sold Highest Sold Dual Agent Carey Hernandez Agent Codes and Commission Rates Buyer's Agent Dual Agency Seller's Agent Location Price Number Sold Total # Sold Prices Price Alpine BA 2.75% American Fork DA 5.50% Cedar Hills Pijuan SA 3.25% Lehi Total Commission by Agent Carey Input for Bonus Percent of List Days on Market Bonus Amount $ 1,000 100% Hernandez 30 Pijuan Sold Price Listing Date Sale Date Days on Market Agent Commission Number City Selling Agent Agent Type Agent Type List Price Bonus DA Pijuan Pijuan American Fork Pijuan Carey Pijuan 705,000 350,000 385,900 Cedar Hills $ 725.000 $ 4/1/2021 6/16/2021 4 Lehi DA $ 350,799 $ 4/1/2021 5/25/2021 DA $ 385,900 $ 4/1/2021 4/30/2021 6. Alpine Lehi SA $ 500,000 $ 345.999 $ 495.800 4/3/2021 6/10/2021 7 ВА 345,000 4/3/2021 6/1/2021 11 American Fork Hernandez DA $ 325,000 $ 320,000 4/12/2021 5/30/2021 12 Alpine Alpine American Fork Pijuan Lehi American Fork Pijuan Alpine Pijuan Carey DA $ 750,250 $ 725,900 4/12/2021 11/1/2021 645.000 $ 425,000 $ 17 DA $ 643,000 4/18/2021 6/23/2021 19 ВА 2$ 415,000 4/18/2021 6/30/2021 21 Pijuan SA 325,000 $ 330,000 4/28/2021 6/1/2021 24 SA $ 425,815 $ 418,000 4/28/2021 9/15/2021 27 Pijuan DA 700,000 $ 705,000 4/30/2021 6/19/2021 30 Lehi Hernandez SA $ 365,000 $ 363,000 5/1/2021 6/18/2021 32 Cedar Hills Carey Carey Hernandez SA $ 885,500 $ 800,000 5/1/2021 6/6/2021 Cedar Hills Cedar Hills 34 SA $ 610,000 $ 600,000 5/1/2021 6/26/2021 35 DA $ 560,700 $ 565,000 5/1/2021 5/30/2021 36 American Fork Hernandez BA 450,000 $ 450,000 5/2/2021 9/30/2021 American Fork Carey Carey Hernandez 43 BA $ 375,000 $ 372,500 5/6/2021 5/15/2021 46 Cedar Hills SA 6/9/2021 $ 650,000 $ 545.000 $ 625,750 5/15/2021 Alpine Lehi 560,000 320,000 48 DA $ 5/22/2021 6/16/2021 53 Carey Carey Hernandez SA $ 315,750 $ 5/28/2021 6/18/2021 61 Lehi DA $ 300,000 $ 300,000 6/2/2021 9/2/2021 69 Alpine DA $ 589,000 $ 595,000 6/18/2021 7/8/2021 70 American Fork Hernandez DA $ 345,670 $ 345,000 6/20/2021 9/30/2021 550,000 485,000 400,000 73 6/24/2021 6/30/2021 Carey Carey American Fork Carey American Fork Pijuan Cedar Hills BA $ 555,000 $ 75 Alpine ВА 2$ 475,000 $ 6/24/2021 9/18/2021 $ 400,000 $ $ 380,500 $ 6/30/2021 7/15/2021 6/30/2021 78 DA 80 SA $ 365,750 9/1/2021 Details Map Loan
Transcribed Image Text:Average Sold Total Sold Highest Sold Dual Agent Carey Hernandez Agent Codes and Commission Rates Buyer's Agent Dual Agency Seller's Agent Location Price Number Sold Total # Sold Prices Price Alpine BA 2.75% American Fork DA 5.50% Cedar Hills Pijuan SA 3.25% Lehi Total Commission by Agent Carey Input for Bonus Percent of List Days on Market Bonus Amount $ 1,000 100% Hernandez 30 Pijuan Sold Price Listing Date Sale Date Days on Market Agent Commission Number City Selling Agent Agent Type Agent Type List Price Bonus DA Pijuan Pijuan American Fork Pijuan Carey Pijuan 705,000 350,000 385,900 Cedar Hills $ 725.000 $ 4/1/2021 6/16/2021 4 Lehi DA $ 350,799 $ 4/1/2021 5/25/2021 DA $ 385,900 $ 4/1/2021 4/30/2021 6. Alpine Lehi SA $ 500,000 $ 345.999 $ 495.800 4/3/2021 6/10/2021 7 ВА 345,000 4/3/2021 6/1/2021 11 American Fork Hernandez DA $ 325,000 $ 320,000 4/12/2021 5/30/2021 12 Alpine Alpine American Fork Pijuan Lehi American Fork Pijuan Alpine Pijuan Carey DA $ 750,250 $ 725,900 4/12/2021 11/1/2021 645.000 $ 425,000 $ 17 DA $ 643,000 4/18/2021 6/23/2021 19 ВА 2$ 415,000 4/18/2021 6/30/2021 21 Pijuan SA 325,000 $ 330,000 4/28/2021 6/1/2021 24 SA $ 425,815 $ 418,000 4/28/2021 9/15/2021 27 Pijuan DA 700,000 $ 705,000 4/30/2021 6/19/2021 30 Lehi Hernandez SA $ 365,000 $ 363,000 5/1/2021 6/18/2021 32 Cedar Hills Carey Carey Hernandez SA $ 885,500 $ 800,000 5/1/2021 6/6/2021 Cedar Hills Cedar Hills 34 SA $ 610,000 $ 600,000 5/1/2021 6/26/2021 35 DA $ 560,700 $ 565,000 5/1/2021 5/30/2021 36 American Fork Hernandez BA 450,000 $ 450,000 5/2/2021 9/30/2021 American Fork Carey Carey Hernandez 43 BA $ 375,000 $ 372,500 5/6/2021 5/15/2021 46 Cedar Hills SA 6/9/2021 $ 650,000 $ 545.000 $ 625,750 5/15/2021 Alpine Lehi 560,000 320,000 48 DA $ 5/22/2021 6/16/2021 53 Carey Carey Hernandez SA $ 315,750 $ 5/28/2021 6/18/2021 61 Lehi DA $ 300,000 $ 300,000 6/2/2021 9/2/2021 69 Alpine DA $ 589,000 $ 595,000 6/18/2021 7/8/2021 70 American Fork Hernandez DA $ 345,670 $ 345,000 6/20/2021 9/30/2021 550,000 485,000 400,000 73 6/24/2021 6/30/2021 Carey Carey American Fork Carey American Fork Pijuan Cedar Hills BA $ 555,000 $ 75 Alpine ВА 2$ 475,000 $ 6/24/2021 9/18/2021 $ 400,000 $ $ 380,500 $ 6/30/2021 7/15/2021 6/30/2021 78 DA 80 SA $ 365,750 9/1/2021 Details Map Loan
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps with 3 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Intermediate SQL concepts
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS