answer the questions based off the excel tables
File Home Insert Draw Design Layout References Mailings Review View Help Share Comments X Arial 9 ~A Aar Po AL 1 AaBb CcDd AaBb C AaBbCcl AaBbCcDdl AaBbCcDdl Emphasis Heading 1 Heading 2 Normal I Normal l... = Find Sboc Replace W Select v Paste Dictate Sensitivity Editor BI U v ab x, x? Apv A Clipboard Font Paragraph Styles Editing Voice Sensitivity Editor 1 8 On the FORMULAS&FUNCTIONS sheet: in column D, use a VLOOKUP function to return the Rent for each property. The Rent is determined by the number of rooms in the Rental Price/Cost table. 2 8 In column E, use a VLOOKUP function to return the Cost of Maintenance for each property. The Cost of Maintenance is determined by the number of rooms in the Rental Price/Cost table. 3 8 In column F, use a VLOOKUP function to return the Tax for each property. The Tax is determined by the Rent Amount in the Tax Table. 4 8 In column G, use an IF function to determine the amount of Profit according to this logic: if the property is occupied ("Yes" in column C), then the Profit is Rent (Column D) minus Cost of Maintenance (Column E). If the property does not meet those conditions, then Profit is zero. 5 8 In column H, use an IF(AND) function to determine the amount of Tax Paid according to this logic: if the property has more than 2 rooms (Column B > 2) AND is occupied ("Yes" in column C), then Profit (Column G) times Tax (Column F). If the property does not meet those conditions, then Tax Paid is zero. 6 7 In column I, use an IF(OR) function to determine the amount of Discount according to this logic: if the property has 1 room (Column B = 1) OR is not occupied (No in column C), then Cost of Maintenance (Column E) times Tax (Column F). If the property does not meet those conditions, then Discount is zero. 7 7 In column J, use an IF(IF) function to return the Tax Category according to this logic: If the Tax Paid is less than $30 (Column H 100), then return the text "high". If the Tax Paid does not meet either of those conditions, then return the text "medium". 8 6 ON the IMPORT sheet: add a column to the left of the column. Label the new column Region. The leftmost character of the RMA code indicates the region. Use a parsing function to exact the letter. Use the extracted letter in a VLOOKUP to return the region from the DEGIONS table Page 1 of 2 770 words BX C Focus na + 100% File Home Insert Draw Design Layout References Mailings Review View Help Share Comments X Arial - 9 ~A Aar Po E AJ AaBbCcDd AaBb C AaBbCcl AaBbCcDdl AaBbCcDdl Emphasis Heading 1 Heading 2 1 Normal I Normal l... = Find Sec Replace A Select Paste B IU v ab x, x? A PA Editor Dictate 15 Sensitivity ..... Clipboard Font Editing Voice Sensitivity Editor 9 4 Paragraph Styles Add a column to the left of SERIAL NUMBER column. Label the new column Product Line. Use the two-letter code in PCode column in a VLOOKUP to return the Product Line from the PRODUCTS table. Copy the result as VALUES ONLY and then delete the unnecessary PCode column. 10 4 In column labeled START DATE, the date is stored as text and has a two-letter code attached on the left. Use Text-to-Columns to leave only the date formatted as M/D/Y. 11 4 In column labeled SHIP DATE, the date is stored as text and has a two or three-letter code and a hyphen attached on the left. Use Text-to-Columns to leave only the date formatted as M/D/Y. Created On: 07/05/2019 1 Midterm 2 Comprehensive Prep Grader - Instructions Excel 2016 Project Step Instructions Points Possible 12 7 Add a column to the far right of the data. Name this column TURNAROUND. Use the function NETWORKDAYS to calculate the turnaround time. File Home Insert Draw Design Layout References Mailings Review View Help Share Comments AL Arial v9 ~ A Aar Po BI U v ab x, x? A vpvA AaBbCcDd AaBb C AaBbCcl AaBbCcDdl AaBbCcDdl Emphasis Heading 1 Heading 2 1 Normal 1 Normal l... O Find Soc Replace A Select v Paste Dictate Sensitivity Editor Clipboard Font Paragraph IN Styles Editing Voice Sensitivity Editor 13 0 Create a Pivot Table from the IMPORT sheet data. Create the PivotTable on an existing sheet. Place the Pivot Table in cell A1 of the sheet named PIVOT. 14 0 Answer questions 15-21 using the Pivot Table. Copy the numerical answer to each question into the corresponding green cell in Column Q of the PIVOT sheet. 15 What is the SUM of the Cost of all records in the data set? Paste the answer in Q3. 3 16 3 What is the AVERAGE of the Turn Around of all records in the data set.? Format to 2 decimal places. Paste the answer in Q5. 17 What is the SUM of the Cost for Failure Code "No Problem Found"? Paste the answer in Q7. 3 18 3 What is the SUM of the Cost for Failure Code "Optical Problem" with a Ship Date in the month of July? Paste the answer in Q9. 19 3 What was the Average Turnaround time for Printer in the East? Round to 2 decimal places. Paste the answer in Q11. 20 What was the SUM of Cost for Computer in the South? Paste the answer in Q13. 3 21 3 What was the SUM of Cost for all records with Start date in the first quarter of the year? Paste the answer in Q15. Total Points 100 Page 2 of 2 770 words D Focus + DO le 100% File Home Insert Draw Page Layout Formulas Data Review View Help B Share Comments fx A 3 Calculate Now E Trace Precedents for Show Formulas a Trace Dependents Error Checking FRemove Arrows Evaluate Formula Insert Function Define Name Use in Formula Name Manager O Create from Selection Watch AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference Trig Functions Calculation Options Calculate Sheet Window Function Library Defined Names Formula Auditing Calculation AND fr =IFC A B F L M N o Tax Number of Rooms Occupied 1 Yes 4 Yes 2 Property Address 3 528 Ivy Court 4 395 4th Street South 5 147 Cross Street 6 787 8th Street 7 97 Walnut Street 8 966 B Street D E Cost of Rent Maintenence $350 $125 $1,200 $275 $900 $225 $350 $125 $600 $150 $900 $225 H K Tax Profit Tax Paid Discount Category $225 =1F/ $925 IF(logical_test, [value_if_true], [value_if_false]) $675 $0 $0 $675 3 Yes No 1 2 No Yes 3 9 10 11 12 13 Rental Price/Cost Cost of Maintene Rooms Rent Deposit nce 4 $1,200 $600 $275 3 $900 $450 $225 2 $600 $300 $150 1 $350 $200 $125 14 15 16 17 18 Formulas&Functions Import Tables Pivot + Enter + 100% File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments fx M 10 Calculate Now Insert Function ? A AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference Trig Functions Define Name Cause in Formula v Name Manager EO Create from Selection & Trace Precedents Var Show Formulas Fa Trace Dependents Error Checking v FRemove Arrows Evaluate Formula Formula Auditing LOS Watch Window Calculation Options Calculate Sheet Function Library Defined Names Calculation AND fc =IFC E F H 1 K L M N o P Q 12 13 A B D Rental Price/Cost Cost of Maintene Rooms Rent Deposit nce 4 $1,200 $600 $275 3 $900 $450 $225 2 $600 $300 $150 1 $350 $200 $125 IF(logical_test, [value_if_true], [value_if_false]) 14 15 16 17 18 19 Tax Table 20 Rent Amount 300 21 Tax Rate 3% 5% 11% 650 22 23 1000 24 25 26 27 Formulas&Functions Import Tables Pivot + Enter + 100% File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments fx Calculate Now ? AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference Trig Functions Define Name Cause in Formula v Name Manager EO Create from Selection & Trace Precedents Var Show Formulas Trace Dependents Error Checking FRemove Arrows Evaluate Formula Insert Function LOS Watch Window Calculation Options Calculate Sheet Function Library Defined Names Formula Auditing Calculation A1 : fx =IF(Import! A B 1 J K L M N o P Q R S 1 RMA Pcode IF(logical_test, [value_if_true], [value_if_false]) 2 E4763958! PO 3 E4733425, CS 4 E4729114! PO 5 W4730404 PO 6 S4743795.CL 7 S4742158.CL 8 S4745354. CD 9 M4769816 PO 10 S4729907: CL 11 E47546751 CL 12 E4743101CL 13 E4744309: PO 14 W4761815 CD 15 E4761707. PO 16 E4756579! CL 17 547619291 CD 18 W475725E CL 19 S4762665.CO 20 W4732431PD 21 D E F G H Serial Nun Failure Co Start Date Ship Date Quan Cost L8430087: Optical PrePO11/29/2014 POC-12/5/ 202 7272 B3710376: Electrical FCS12/27/2014 CSP-12/31 57 1710 Q6120965 Electrical F P05/21/2014 PO-5/26/2 335 2010 183909859 Electrical EP09/10/2014 POC-9/15/ 96 864 K7631951 Mechanic: CL11/13/2014 CLC-11/21 149 4321 J08115555 Mechanic: CL6/4/2014 CL-6/11/2 119 4641 07504087: Mechanic: CD11/8/2014 CDP-11/14 289 6647 A4576595 Software P04/11/2014 PO-4/21/2 135 2565 Q2383668 Electrical FCL5/28/2014 CL-6/5/20 59 767 M4452574 No Proble MT2/3/2014 MT-2/4/21 194 5626 KO294939: Mechanic: CL9/11/2014 CLP-9/16/ 118 4484 D5314532 Mechanic: PO6/13/2014 PO-6/16/2 310 6820 H2298572 Optical Pro CD7/8/2014 CDP-7/16/ 193 1544 C7742886! Optical PrePO1/15/2014 PO-1/20/2 67 1206 Q8116187 No Proble CL7/4/2014 CL-7/7/20 260 4940 E6302969 Optical Pro CD7/29/2014 CDC-8/4/2 308 7084 Q2367575 No Proble CL1/25/2014 CL-1/29/2 137 1096 T3806506 Optical PreCO3/6/2014 CO-3/12/2 214 7490 J11488534 Electrical EPD6/20/2014 PD-6/30/2 183 5307 Formulas&Functions Import Tables Pivot + Point E + 100% File Home Insert Draw Page Layout Formulas Data Review View Help B Share Comments fx a l01 Calculate Now Define Name CiUse in Formula Name Manager Eo Create from Selection Insert Function AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference v Trig Functions v Function Library E, Trace Precedents Vax Show Formulas Trace Dependents Error Checking - FRemove Arrows Evaluate Formula Formula Auditing Watch Window Calculation Calculate Sheet Options Defined Names Calculation A1 fr =IF(Tables! D F G H. | J K L M . N 0 P Q R S Code CD CL CO A B 1 Site Cod Region 2 Atlanta South 3 Chicago Midwest 4 E East 5 Los Angele West 6 M Midwest 7 New York East 8 S South 9 W West E Produc Computer Computer Computer Computer Printer Printer Printer Printer IF(logical_test, [value_if_true], [value_if_false]) CS PD PI PL PO 10 Hammon on a Sa 11 14 15 16 17 18 19 20 Formulas&Functions Import Tables Pivot Point + 100% O Type here to search 0 W H 4:53 PM 12/29/2020 File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments fx Calculate Now Insert Function A AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference v Trig Functions v Function Library Define Name Cause in Formula Name Manager Bo Create from Selection E, Trace Precedents Vix Show Formulas Sa Trace Dependents Error Checking ~ FRemove Arrows ~ Evaluate Formula Formula Auditing Watch Window Calculation Options Calculate Sheet Defined Names Calculation A1 fic =IF(Pivot! . B D E F. G H. | J K L M N 0 Q R S T 1 2 3 4 Question 15 IF(logical_test, [value_if_true], [value_if_false]) Question 16 5 6 7 Question 17 8 9 Question 18 10 11 Question 19 12 13 Question 20 14 15 Question 21 16 17 18 19 20 21 Formulas&Functions Import Tables Pivot + File Home Insert Draw Design Layout References Mailings Review View Help Share Comments X Arial 9 ~A Aar Po AL 1 AaBb CcDd AaBb C AaBbCcl AaBbCcDdl AaBbCcDdl Emphasis Heading 1 Heading 2 Normal I Normal l... = Find Sboc Replace W Select v Paste Dictate Sensitivity Editor BI U v ab x, x? Apv A Clipboard Font Paragraph Styles Editing Voice Sensitivity Editor 1 8 On the FORMULAS&FUNCTIONS sheet: in column D, use a VLOOKUP function to return the Rent for each property. The Rent is determined by the number of rooms in the Rental Price/Cost table. 2 8 In column E, use a VLOOKUP function to return the Cost of Maintenance for each property. The Cost of Maintenance is determined by the number of rooms in the Rental Price/Cost table. 3 8 In column F, use a VLOOKUP function to return the Tax for each property. The Tax is determined by the Rent Amount in the Tax Table. 4 8 In column G, use an IF function to determine the amount of Profit according to this logic: if the property is occupied ("Yes" in column C), then the Profit is Rent (Column D) minus Cost of Maintenance (Column E). If the property does not meet those conditions, then Profit is zero. 5 8 In column H, use an IF(AND) function to determine the amount of Tax Paid according to this logic: if the property has more than 2 rooms (Column B > 2) AND is occupied ("Yes" in column C), then Profit (Column G) times Tax (Column F). If the property does not meet those conditions, then Tax Paid is zero. 6 7 In column I, use an IF(OR) function to determine the amount of Discount according to this logic: if the property has 1 room (Column B = 1) OR is not occupied (No in column C), then Cost of Maintenance (Column E) times Tax (Column F). If the property does not meet those conditions, then Discount is zero. 7 7 In column J, use an IF(IF) function to return the Tax Category according to this logic: If the Tax Paid is less than $30 (Column H 100), then return the text "high". If the Tax Paid does not meet either of those conditions, then return the text "medium". 8 6 ON the IMPORT sheet: add a column to the left of the column. Label the new column Region. The leftmost character of the RMA code indicates the region. Use a parsing function to exact the letter. Use the extracted letter in a VLOOKUP to return the region from the DEGIONS table Page 1 of 2 770 words BX C Focus na + 100% File Home Insert Draw Design Layout References Mailings Review View Help Share Comments X Arial - 9 ~A Aar Po E AJ AaBbCcDd AaBb C AaBbCcl AaBbCcDdl AaBbCcDdl Emphasis Heading 1 Heading 2 1 Normal I Normal l... = Find Sec Replace A Select Paste B IU v ab x, x? A PA Editor Dictate 15 Sensitivity ..... Clipboard Font Editing Voice Sensitivity Editor 9 4 Paragraph Styles Add a column to the left of SERIAL NUMBER column. Label the new column Product Line. Use the two-letter code in PCode column in a VLOOKUP to return the Product Line from the PRODUCTS table. Copy the result as VALUES ONLY and then delete the unnecessary PCode column. 10 4 In column labeled START DATE, the date is stored as text and has a two-letter code attached on the left. Use Text-to-Columns to leave only the date formatted as M/D/Y. 11 4 In column labeled SHIP DATE, the date is stored as text and has a two or three-letter code and a hyphen attached on the left. Use Text-to-Columns to leave only the date formatted as M/D/Y. Created On: 07/05/2019 1 Midterm 2 Comprehensive Prep Grader - Instructions Excel 2016 Project Step Instructions Points Possible 12 7 Add a column to the far right of the data. Name this column TURNAROUND. Use the function NETWORKDAYS to calculate the turnaround time. File Home Insert Draw Design Layout References Mailings Review View Help Share Comments AL Arial v9 ~ A Aar Po BI U v ab x, x? A vpvA AaBbCcDd AaBb C AaBbCcl AaBbCcDdl AaBbCcDdl Emphasis Heading 1 Heading 2 1 Normal 1 Normal l... O Find Soc Replace A Select v Paste Dictate Sensitivity Editor Clipboard Font Paragraph IN Styles Editing Voice Sensitivity Editor 13 0 Create a Pivot Table from the IMPORT sheet data. Create the PivotTable on an existing sheet. Place the Pivot Table in cell A1 of the sheet named PIVOT. 14 0 Answer questions 15-21 using the Pivot Table. Copy the numerical answer to each question into the corresponding green cell in Column Q of the PIVOT sheet. 15 What is the SUM of the Cost of all records in the data set? Paste the answer in Q3. 3 16 3 What is the AVERAGE of the Turn Around of all records in the data set.? Format to 2 decimal places. Paste the answer in Q5. 17 What is the SUM of the Cost for Failure Code "No Problem Found"? Paste the answer in Q7. 3 18 3 What is the SUM of the Cost for Failure Code "Optical Problem" with a Ship Date in the month of July? Paste the answer in Q9. 19 3 What was the Average Turnaround time for Printer in the East? Round to 2 decimal places. Paste the answer in Q11. 20 What was the SUM of Cost for Computer in the South? Paste the answer in Q13. 3 21 3 What was the SUM of Cost for all records with Start date in the first quarter of the year? Paste the answer in Q15. Total Points 100 Page 2 of 2 770 words D Focus + DO le 100% File Home Insert Draw Page Layout Formulas Data Review View Help B Share Comments fx A 3 Calculate Now E Trace Precedents for Show Formulas a Trace Dependents Error Checking FRemove Arrows Evaluate Formula Insert Function Define Name Use in Formula Name Manager O Create from Selection Watch AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference Trig Functions Calculation Options Calculate Sheet Window Function Library Defined Names Formula Auditing Calculation AND fr =IFC A B F L M N o Tax Number of Rooms Occupied 1 Yes 4 Yes 2 Property Address 3 528 Ivy Court 4 395 4th Street South 5 147 Cross Street 6 787 8th Street 7 97 Walnut Street 8 966 B Street D E Cost of Rent Maintenence $350 $125 $1,200 $275 $900 $225 $350 $125 $600 $150 $900 $225 H K Tax Profit Tax Paid Discount Category $225 =1F/ $925 IF(logical_test, [value_if_true], [value_if_false]) $675 $0 $0 $675 3 Yes No 1 2 No Yes 3 9 10 11 12 13 Rental Price/Cost Cost of Maintene Rooms Rent Deposit nce 4 $1,200 $600 $275 3 $900 $450 $225 2 $600 $300 $150 1 $350 $200 $125 14 15 16 17 18 Formulas&Functions Import Tables Pivot + Enter + 100% File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments fx M 10 Calculate Now Insert Function ? A AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference Trig Functions Define Name Cause in Formula v Name Manager EO Create from Selection & Trace Precedents Var Show Formulas Fa Trace Dependents Error Checking v FRemove Arrows Evaluate Formula Formula Auditing LOS Watch Window Calculation Options Calculate Sheet Function Library Defined Names Calculation AND fc =IFC E F H 1 K L M N o P Q 12 13 A B D Rental Price/Cost Cost of Maintene Rooms Rent Deposit nce 4 $1,200 $600 $275 3 $900 $450 $225 2 $600 $300 $150 1 $350 $200 $125 IF(logical_test, [value_if_true], [value_if_false]) 14 15 16 17 18 19 Tax Table 20 Rent Amount 300 21 Tax Rate 3% 5% 11% 650 22 23 1000 24 25 26 27 Formulas&Functions Import Tables Pivot + Enter + 100% File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments fx Calculate Now ? AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference Trig Functions Define Name Cause in Formula v Name Manager EO Create from Selection & Trace Precedents Var Show Formulas Trace Dependents Error Checking FRemove Arrows Evaluate Formula Insert Function LOS Watch Window Calculation Options Calculate Sheet Function Library Defined Names Formula Auditing Calculation A1 : fx =IF(Import! A B 1 J K L M N o P Q R S 1 RMA Pcode IF(logical_test, [value_if_true], [value_if_false]) 2 E4763958! PO 3 E4733425, CS 4 E4729114! PO 5 W4730404 PO 6 S4743795.CL 7 S4742158.CL 8 S4745354. CD 9 M4769816 PO 10 S4729907: CL 11 E47546751 CL 12 E4743101CL 13 E4744309: PO 14 W4761815 CD 15 E4761707. PO 16 E4756579! CL 17 547619291 CD 18 W475725E CL 19 S4762665.CO 20 W4732431PD 21 D E F G H Serial Nun Failure Co Start Date Ship Date Quan Cost L8430087: Optical PrePO11/29/2014 POC-12/5/ 202 7272 B3710376: Electrical FCS12/27/2014 CSP-12/31 57 1710 Q6120965 Electrical F P05/21/2014 PO-5/26/2 335 2010 183909859 Electrical EP09/10/2014 POC-9/15/ 96 864 K7631951 Mechanic: CL11/13/2014 CLC-11/21 149 4321 J08115555 Mechanic: CL6/4/2014 CL-6/11/2 119 4641 07504087: Mechanic: CD11/8/2014 CDP-11/14 289 6647 A4576595 Software P04/11/2014 PO-4/21/2 135 2565 Q2383668 Electrical FCL5/28/2014 CL-6/5/20 59 767 M4452574 No Proble MT2/3/2014 MT-2/4/21 194 5626 KO294939: Mechanic: CL9/11/2014 CLP-9/16/ 118 4484 D5314532 Mechanic: PO6/13/2014 PO-6/16/2 310 6820 H2298572 Optical Pro CD7/8/2014 CDP-7/16/ 193 1544 C7742886! Optical PrePO1/15/2014 PO-1/20/2 67 1206 Q8116187 No Proble CL7/4/2014 CL-7/7/20 260 4940 E6302969 Optical Pro CD7/29/2014 CDC-8/4/2 308 7084 Q2367575 No Proble CL1/25/2014 CL-1/29/2 137 1096 T3806506 Optical PreCO3/6/2014 CO-3/12/2 214 7490 J11488534 Electrical EPD6/20/2014 PD-6/30/2 183 5307 Formulas&Functions Import Tables Pivot + Point E + 100% File Home Insert Draw Page Layout Formulas Data Review View Help B Share Comments fx a l01 Calculate Now Define Name CiUse in Formula Name Manager Eo Create from Selection Insert Function AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference v Trig Functions v Function Library E, Trace Precedents Vax Show Formulas Trace Dependents Error Checking - FRemove Arrows Evaluate Formula Formula Auditing Watch Window Calculation Calculate Sheet Options Defined Names Calculation A1 fr =IF(Tables! D F G H. | J K L M . N 0 P Q R S Code CD CL CO A B 1 Site Cod Region 2 Atlanta South 3 Chicago Midwest 4 E East 5 Los Angele West 6 M Midwest 7 New York East 8 S South 9 W West E Produc Computer Computer Computer Computer Printer Printer Printer Printer IF(logical_test, [value_if_true], [value_if_false]) CS PD PI PL PO 10 Hammon on a Sa 11 14 15 16 17 18 19 20 Formulas&Functions Import Tables Pivot Point + 100% O Type here to search 0 W H 4:53 PM 12/29/2020 File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments fx Calculate Now Insert Function A AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference v Trig Functions v Function Library Define Name Cause in Formula Name Manager Bo Create from Selection E, Trace Precedents Vix Show Formulas Sa Trace Dependents Error Checking ~ FRemove Arrows ~ Evaluate Formula Formula Auditing Watch Window Calculation Options Calculate Sheet Defined Names Calculation A1 fic =IF(Pivot! . B D E F. G H. | J K L M N 0 Q R S T 1 2 3 4 Question 15 IF(logical_test, [value_if_true], [value_if_false]) Question 16 5 6 7 Question 17 8 9 Question 18 10 11 Question 19 12 13 Question 20 14 15 Question 21 16 17 18 19 20 21 Formulas&Functions Import Tables Pivot +
