Please just write the formulas of the ones that require it! Thank you

1. Alex Makula is a project manager for the Content Shop, an online firm based in New Haven, Connecticut, that provides written content for websites. Alex is compiling data on the contract writers and editors the company works with, and he asks for your help completing the workbook and analyzing the data. Switch to the Contract Writers worksheet, where Alex created a formula with the VLOOKUP function to look up contractor names by their Contractor ID. Other project managers will use this workbook, and he wants to alert them when they enter an incorrect ID number. In cell B4, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, display "Invalid Contractor ID" as the error text. 2. 3. Alex needs to complete the Writers table in the range A7:127. First, determine whether each contractor can write questions and answers for information websites. In cell G8, enter a formula using the IF function that uses a structured reference to the Years column ([@Years]) to determine if the value is greater than 1. The formula returns the text Yes if true and No if false. Fill the formula into the range G9:G27, if necessary. Contractors who can write reviews must have at least 2 years of experience or an average evaluation rating from Contract Shop managers of 7 or more. In cell H8, enter a formula using the IF and OR functions to determine whether the value in the Years column is greater than or equal to 2 OR whether the value in the Avg Eval column is greater than or equal to 7. Use a structured reference to both columns ([@Years) and [@[Avg Eval]]). The formula returns the text Yes if a contractor meets one or both of the criteria, and it returns No if a contractor meets neither criteria. Fill the formula into the range H9:H27, if necessary. Contractors can take copywriting assignments if they have at least 3 years of experience and a customer score of at least 7. In cell 18, enter a formula using the IF and AND functions to determine whether the value in the Years column is greater than or equal to 3 AND whether the value in the Avg Score column is greater than or equal to 7. Use a structured reference to both columns ([@Years] and [@[Avg Score]]). The formula returns the text Yes if a contractor meets both criteria or the text No if a contractor meets none or only one of the criteria. Fill the formula into the range 19:127, if necessary. 4. 5. 6. Switch to the Editing PivotTable worksheet. It contains the Editing Projects PivotTable, which is based on the Editors table on the Editing Projects worksheet. Display the PivotTable Field List, and then remove the Average of Total Projects field from the Values area. Move the GroupID field so that it appears as the second field in the Rows area to make the PivotTable easier to interpret. Alex wants another way to compare the editing projects by level, but not by group. Collapse the outline in the Editing Projects PivotTable to display the Level names and to hide the Group IDs. Insert a PivotChart based on the Editing Projects PivotTable using the Stacked Column chart type. Resize and reposition the PivotChart so that the upper- left corner is located within cell A12 and the lower-right corner is located within cell F25. Change the PivotChart colors to Monochromatic Palette 5 to coordinate with the Pivot Table. 7. 8. Alex needs to concentrate on copywriting and internal editing projects. Use the Specialty slicer to filter the PivotTable and PivotChart to display only Copywriting and Internal editing projects. Switch to the Platinum Editing Groups worksheet, which includes the Platinum table listing project data for groups at the Platinum level, the highest editing level in the company. Alex wants to display statistics in cells B15 and B17. In cell B15, use the INDEX function to display the value in the first row and first column of the Platinum table. 9. In cell B17, use the SUMIF function and structured references to display the total number of projects completed by groups with the Reviews specialty. 10. Alex wants to compare the Platinum group data for January by specialty. He asks you to create a PivotTable to better manipulate and filter the data. On a new worksheet, create a recommended PivotTable based on the Platinum table that shows the Sum Jan by Specialty. [Mac Hint - Insert a new PivotTable on a new worksheet, adding the Specialty field to the Rows area and the Jan field to the Values area.] Use Platinum Pivot Table for the name of the worksheet containing this PivotTable. Apply Rose, Pivot Style Medium 13 to match the style of the other tables in the workbook. 11. Alex asks you to customize the new PivotTable to show more details and to provide a filter. Add the Total Projects field to the Values area below the Sum of Jan field. Add the Group ID field to the Rows area below the Specialty field. Add the Project Type field to the Filters area. Change the display of subtotals to Show all Subtotals at Bottom of Group. Group. 12. Alex also wants to focus on data for repeat projects only and to display the records for reviews. Filter the PivotTable to show only records with a Repeat project type. Drill down into cell C15 to show all records for Reviewing projects with a Repeat project type on a new worksheet. Use Repeat Reviews as the name of the new worksheet. Apply Red, Table Style Medium 6 to the table. Your workbook should look like the Final Figures on the following pages. Save your changes, Content Shop Contract Writers Years Contractor ID Contractor Name S-567 Jose Saldana 0 $0.25 1 $0.30 2 $0.35 3 $0.40 Rate per Word $0.50 Assignments Reviews Copywriting Years Q&As Contractor ID -701 3-022 3-216 C-147 D-724 =-629 --975 H-108 J-869 _-293 _-452 -443 -508 -692 -733 6-567 6-752 6-939 1-098 W-231 Name Victor Alvarez Doug Bidinger Antoine Bossard Mary Kay Cavanaugh James Dargenio Pilar Flores Rachel Fuller Min Hong Katrina Jackson Jum Li Will Lucht Kerry O'Keefe Liz Payton Patrick Pfeiffer Calvin Purcell Jose Saldana Anthony Scalizzi Martin Schuster Maria Velazquez Latoya Wheeler 2.5 1.0 1.5 4.0 3.0 4.0 0.5 3.5 2.0 3.0 3.0 2.0 0.5 3.0 4.0 4.5 2.5 4.0 2.0 2.0 Rate per Word $0.35 $0.30 $0.30 $0.50 $0.40 $0.50 $0.25 $0.40 $0.35 $0.40 $0.40 $0.35 $0.25 $0.40 $0.50 $0.50 $0.35 $0.50 $0.35 $0.35 Avg Eval 7.50 5.00 6.75 5.50 8.00 8.00 7.50 6.50 5.75 8.00 7.75 5.25 6.25 7.00 8.00 8.50 7.00 6.75 6.00 7.00 Avg Score 7.50 6.00 7.25 8.00 7.50 8.50 10.00 6.50 6.00 8.00 7.50 6.00 6.25 8.50 7.50 9.00 7.50 7.00 6.00 6.75 A B C D E F G H 1 J K Group ID A-2 A-2 A-1 A-1 B-2 C-2 C-2 C-3 C-2 B-1 C-1 B-2 B-2 B-3 B-3 C-3 C-1 A-3 A-1 C-1 A-3 B-1 B-1 C-1 C-2 B-2 A-2 C-3 A-3 B-3 B-1 A-2 B-3 C-3 A-3 A-1 Level - Specialty Platinum Reviews Silver Reviews Silver Q&As Platinum Q&As Platinum Copywriting Bronze Internal Gold Reviews Silver Reviews Silver Q&As Bronze Copywriting Platinum Internal Silver Q&As Q Bronze Internal Platinum Reviews Silver Internal Platinum Copywriting Gold Q&As Silver Copywriting Bronze Reviews Bronze Copywriting Platinum Internal Platinum Q&As Gold Copywriting Silver Reviews Platinum Reviews Gold Copywriting Bronze Q&As Bronze Reviews Bronze Q&As Gold Q&AS Silver Reviews Gold Reviews Bronze Reviews Gold Copywriting Gold Internal Gold Internal Jan 10 8 7 7 10 6 7 8 8 7 7 6 5 8 5 7 6 4 8 8 5 7 6 5 6 3 5 5 7 6 4 4 5 2 5 5 4 Feb 8 9 9 8 7 8 7 7 7 8 6 8 7 6 7 7 7 6 6 8 8 7 8 8 7 5 5 7 6 5 4 5 4 5 5 4 2 2 Projects Mar 11 10 9 9 7 10 9 8 8 8 9 8 10 6 9 6 7 10 6 4 8 5 6 9 6 8 7 8 7 5 8 5 3 6 5 4 Total Projects - 29 27 25 24 24 24 23 23 23 23 22 22 22 20 21 20 20 20 20 20 20 20 19 19 17 18 18 18 18 16 16 14 13 12 12 10 Documentation Contract Writers Editing Projects Editing Pivot Table Platinum Editing Groups Editing Groups - Platinum Level Group ID A-2 A-1 B-2 C-1 B-3 C-3 A-3 B-1 C-2 Level Platinum Platinum Platinum Platinum Platinum Platinum Platinum Platinum Platinum Specialty Project Type Reviews New Q&AS Repeat Copywriting Repeat Internal New Reviews Repeat Copywriting Repeat Internal New Q&As Q Repeat Reviews Repeat Jan 10 7 10 7 8 7 5 7 6 Feb 8 8 7 6 6 7 7 7 6 Projects Mar 11 9 7 9 6 6 7 5 6 Total Projects - 29 24 24 22 20 20 19 19 18 O 1 3 5 Most projects: 5 7 Total reviews: 3 O 1 2 3 4 5 3 1 2 B 4 5 5 5 7 3 Documentation Contract Writers Editing Projects Editing Pivot Table Platinum Editing Groups + 1. Alex Makula is a project manager for the Content Shop, an online firm based in New Haven, Connecticut, that provides written content for websites. Alex is compiling data on the contract writers and editors the company works with, and he asks for your help completing the workbook and analyzing the data. Switch to the Contract Writers worksheet, where Alex created a formula with the VLOOKUP function to look up contractor names by their Contractor ID. Other project managers will use this workbook, and he wants to alert them when they enter an incorrect ID number. In cell B4, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, display "Invalid Contractor ID" as the error text. 2. 3. Alex needs to complete the Writers table in the range A7:127. First, determine whether each contractor can write questions and answers for information websites. In cell G8, enter a formula using the IF function that uses a structured reference to the Years column ([@Years]) to determine if the value is greater than 1. The formula returns the text Yes if true and No if false. Fill the formula into the range G9:G27, if necessary. Contractors who can write reviews must have at least 2 years of experience or an average evaluation rating from Contract Shop managers of 7 or more. In cell H8, enter a formula using the IF and OR functions to determine whether the value in the Years column is greater than or equal to 2 OR whether the value in the Avg Eval column is greater than or equal to 7. Use a structured reference to both columns ([@Years) and [@[Avg Eval]]). The formula returns the text Yes if a contractor meets one or both of the criteria, and it returns No if a contractor meets neither criteria. Fill the formula into the range H9:H27, if necessary. Contractors can take copywriting assignments if they have at least 3 years of experience and a customer score of at least 7. In cell 18, enter a formula using the IF and AND functions to determine whether the value in the Years column is greater than or equal to 3 AND whether the value in the Avg Score column is greater than or equal to 7. Use a structured reference to both columns ([@Years] and [@[Avg Score]]). The formula returns the text Yes if a contractor meets both criteria or the text No if a contractor meets none or only one of the criteria. Fill the formula into the range 19:127, if necessary. 4. 5. 6. Switch to the Editing PivotTable worksheet. It contains the Editing Projects PivotTable, which is based on the Editors table on the Editing Projects worksheet. Display the PivotTable Field List, and then remove the Average of Total Projects field from the Values area. Move the GroupID field so that it appears as the second field in the Rows area to make the PivotTable easier to interpret. Alex wants another way to compare the editing projects by level, but not by group. Collapse the outline in the Editing Projects PivotTable to display the Level names and to hide the Group IDs. Insert a PivotChart based on the Editing Projects PivotTable using the Stacked Column chart type. Resize and reposition the PivotChart so that the upper- left corner is located within cell A12 and the lower-right corner is located within cell F25. Change the PivotChart colors to Monochromatic Palette 5 to coordinate with the Pivot Table. 7. 8. Alex needs to concentrate on copywriting and internal editing projects. Use the Specialty slicer to filter the PivotTable and PivotChart to display only Copywriting and Internal editing projects. Switch to the Platinum Editing Groups worksheet, which includes the Platinum table listing project data for groups at the Platinum level, the highest editing level in the company. Alex wants to display statistics in cells B15 and B17. In cell B15, use the INDEX function to display the value in the first row and first column of the Platinum table. 9. In cell B17, use the SUMIF function and structured references to display the total number of projects completed by groups with the Reviews specialty. 10. Alex wants to compare the Platinum group data for January by specialty. He asks you to create a PivotTable to better manipulate and filter the data. On a new worksheet, create a recommended PivotTable based on the Platinum table that shows the Sum Jan by Specialty. [Mac Hint - Insert a new PivotTable on a new worksheet, adding the Specialty field to the Rows area and the Jan field to the Values area.] Use Platinum Pivot Table for the name of the worksheet containing this PivotTable. Apply Rose, Pivot Style Medium 13 to match the style of the other tables in the workbook. 11. Alex asks you to customize the new PivotTable to show more details and to provide a filter. Add the Total Projects field to the Values area below the Sum of Jan field. Add the Group ID field to the Rows area below the Specialty field. Add the Project Type field to the Filters area. Change the display of subtotals to Show all Subtotals at Bottom of Group. Group. 12. Alex also wants to focus on data for repeat projects only and to display the records for reviews. Filter the PivotTable to show only records with a Repeat project type. Drill down into cell C15 to show all records for Reviewing projects with a Repeat project type on a new worksheet. Use Repeat Reviews as the name of the new worksheet. Apply Red, Table Style Medium 6 to the table. Your workbook should look like the Final Figures on the following pages. Save your changes, Content Shop Contract Writers Years Contractor ID Contractor Name S-567 Jose Saldana 0 $0.25 1 $0.30 2 $0.35 3 $0.40 Rate per Word $0.50 Assignments Reviews Copywriting Years Q&As Contractor ID -701 3-022 3-216 C-147 D-724 =-629 --975 H-108 J-869 _-293 _-452 -443 -508 -692 -733 6-567 6-752 6-939 1-098 W-231 Name Victor Alvarez Doug Bidinger Antoine Bossard Mary Kay Cavanaugh James Dargenio Pilar Flores Rachel Fuller Min Hong Katrina Jackson Jum Li Will Lucht Kerry O'Keefe Liz Payton Patrick Pfeiffer Calvin Purcell Jose Saldana Anthony Scalizzi Martin Schuster Maria Velazquez Latoya Wheeler 2.5 1.0 1.5 4.0 3.0 4.0 0.5 3.5 2.0 3.0 3.0 2.0 0.5 3.0 4.0 4.5 2.5 4.0 2.0 2.0 Rate per Word $0.35 $0.30 $0.30 $0.50 $0.40 $0.50 $0.25 $0.40 $0.35 $0.40 $0.40 $0.35 $0.25 $0.40 $0.50 $0.50 $0.35 $0.50 $0.35 $0.35 Avg Eval 7.50 5.00 6.75 5.50 8.00 8.00 7.50 6.50 5.75 8.00 7.75 5.25 6.25 7.00 8.00 8.50 7.00 6.75 6.00 7.00 Avg Score 7.50 6.00 7.25 8.00 7.50 8.50 10.00 6.50 6.00 8.00 7.50 6.00 6.25 8.50 7.50 9.00 7.50 7.00 6.00 6.75 A B C D E F G H 1 J K Group ID A-2 A-2 A-1 A-1 B-2 C-2 C-2 C-3 C-2 B-1 C-1 B-2 B-2 B-3 B-3 C-3 C-1 A-3 A-1 C-1 A-3 B-1 B-1 C-1 C-2 B-2 A-2 C-3 A-3 B-3 B-1 A-2 B-3 C-3 A-3 A-1 Level - Specialty Platinum Reviews Silver Reviews Silver Q&As Platinum Q&As Platinum Copywriting Bronze Internal Gold Reviews Silver Reviews Silver Q&As Bronze Copywriting Platinum Internal Silver Q&As Q Bronze Internal Platinum Reviews Silver Internal Platinum Copywriting Gold Q&As Silver Copywriting Bronze Reviews Bronze Copywriting Platinum Internal Platinum Q&As Gold Copywriting Silver Reviews Platinum Reviews Gold Copywriting Bronze Q&As Bronze Reviews Bronze Q&As Gold Q&AS Silver Reviews Gold Reviews Bronze Reviews Gold Copywriting Gold Internal Gold Internal Jan 10 8 7 7 10 6 7 8 8 7 7 6 5 8 5 7 6 4 8 8 5 7 6 5 6 3 5 5 7 6 4 4 5 2 5 5 4 Feb 8 9 9 8 7 8 7 7 7 8 6 8 7 6 7 7 7 6 6 8 8 7 8 8 7 5 5 7 6 5 4 5 4 5 5 4 2 2 Projects Mar 11 10 9 9 7 10 9 8 8 8 9 8 10 6 9 6 7 10 6 4 8 5 6 9 6 8 7 8 7 5 8 5 3 6 5 4 Total Projects - 29 27 25 24 24 24 23 23 23 23 22 22 22 20 21 20 20 20 20 20 20 20 19 19 17 18 18 18 18 16 16 14 13 12 12 10 Documentation Contract Writers Editing Projects Editing Pivot Table Platinum Editing Groups Editing Groups - Platinum Level Group ID A-2 A-1 B-2 C-1 B-3 C-3 A-3 B-1 C-2 Level Platinum Platinum Platinum Platinum Platinum Platinum Platinum Platinum Platinum Specialty Project Type Reviews New Q&AS Repeat Copywriting Repeat Internal New Reviews Repeat Copywriting Repeat Internal New Q&As Q Repeat Reviews Repeat Jan 10 7 10 7 8 7 5 7 6 Feb 8 8 7 6 6 7 7 7 6 Projects Mar 11 9 7 9 6 6 7 5 6 Total Projects - 29 24 24 22 20 20 19 19 18 O 1 3 5 Most projects: 5 7 Total reviews: 3 O 1 2 3 4 5 3 1 2 B 4 5 5 5 7 3 Documentation Contract Writers Editing Projects Editing Pivot Table Platinum Editing Groups +