New Perspectives Excel 2019 | End of module 7 SAM EXAM In cell I4, enter a formula using the AVERAGEIF function to calculate the average Compensation values found in range E4:E16 for records with a Performance Rating value of 5 in range F4:F16

Subject:MS ExcelPrice: Bought3

Share With

New Perspectives Excel 2019 | End of module 7 SAM EXAM

1. In cell I4, enter a formula using the AVERAGEIF function to calculate the average Compensation values found in range E4:E16 for records with a Performance Rating value of 5 in range F4:F16.
2. Create a calculated field in the Attendance PivotTable, naming the field Q1, that totals the values in the January, February, and March fields.
3. In cell F4, create a formula using the HLOOKUP function to determine the Cost per participant for the workshop based on the Workshop ID in cell C4. Use range B16:E17 as the lookup table, and the Cost per participant listed in row 2 of the lookup table. Do not enter a value for the optional range_lookup argument.
4. In cell C6, enter a formula using the RATE function to calculate the interest rate using the investment parameters in Scenario C. Function arguments should include cell E6 as the nper argument, cell F6 as the pmt argument, cell B6 as the PV argument, and cell G6 as the FV argument.
5. In cell K9, enter a formula using the INDEX function to find the value in row 7, column 7 of range A3:H24.
6. In cell J3, create a formula using the MATCH function that returns the location of the Nickname of Jade in cells C5:C25.
7. In cell C17, create a nested formula using the INDEX and MATCH functions that returns the Number of participants for the Bedroom workshop type.
8. In cell F4, enter a formula, using a nested IF function, to calculate the Bonus Amount using the following function arguments: enter D4=3 as the Logical_test argument, enter E4*.1 as the Value_if_true argument, and enter IF(D4=2, E4*.05, 0) as the Value_if_false argument.
9. In cell G4, enter a formula using the AND function, with D4>3 as the Logical1 condition and E4>3 as the Logical2 condition. Excel will return a value of TRUE if both conditions are met and a value of FALSE if one or both of the conditions are not met.
10. In cell G4, enter a formula using the OR function that returns a value of TRUE if either the condition D4>7 or the condition F4>3 is met.
11. In cell I4, enter a formula using the AVERAGEIFS function to calculate the average Compensation values found in range E4:E16 for records with a Position value of Master in range C4:C16 and a Specialty value of Kitchen in range D4:D16.
12. In cell C5, enter a formula using the IFS function that returns a value of P1 if cell B5 is greater than 50,000, a value of P2 if cell B5 is greater than 25,000, and a value of P3 for all other results.
13. In cell J5, enter a formula using the COUNTIF function to count the number of cells in range G4:G24 with a value of Kitchen.
14. In cell K1, enter a formula using the MAXIFS function to find the No. Participants where the Cost is $15 in the City of Atlanta.
15. In cell K1, enter a formula using the MINIFS function to find the minimum No. Participants where the Cost is $10 in the City of Atlanta.
16. In cell G6, enter a formula using the SUMIF function to search range A4:A18 for records with a City value of Atlanta, then total the Participants Registered values listed in range D4:D18 for those records that meet the criteria.
17. Add the State field to the Workshop Participants PivotTable in the COLUMNS area of the PivotTable Fields task pane.
18. In the PivotTable Fields pane, add the February and March fields to the PivotTable, placing the fields in the VALUES area of the pane.
19. Add the State field to the Filters area of the PivotTable Fields pane.

20. Add the Category field to the Rows area of the PivotTable Fields pane.

21. Create a PivotTable on a new worksheet using Power Pivot. Add the Workshop ID, Workshop, and All Workshops fields from the ScheduledWorkshops table to the PivotTable, then add the Total Cost field from the WorkshopCosts table to the PivotTable.
22. Show and then hide the PivotTable Fields pane.
23. Change the layout of the Workshop Participants PivotTable to the Outline layout.
24. Filter the Workshop Participants PivotTable using the Report filter for the State field to display only those records with a State of value of TN (Tennessee).
25. Filter the PivotChart on the State field to display only records with a value of Arizona.
26. Use the Recommended PivotTables command to insert a blank PivotTable in a new worksheet using the Workshop Participants table as the source range.
27. In the PivotTable, change the custom name of the VALUE field Average of 2020 to 2020 Average Cost as the new field name.
28. Create and apply a custom format code to cells in range B3:D3 using the yyyy format.
29. Apply the Number format with 0 (zero) decimal places to the Column chart data labels, and then close the pane.
30. In the PivotTable, change the summary function used by the January VALUE field from Sum to Average.
31. In the PivotTable Fields pane, move the State field from the Columns area to the Rows area, placing it above the Most Recent Workshop field. Move the Rating field from the Columns area to the Rows area, placing it Below the Most Recent Workshop field.
32. Apply the Light Orange, Pivot Style Medium 21 style to the Workshop Participants PivotTable (Medium section, row 3, last option).
33. Add the alternative text Cost forecast for three years and four cities to the chart.
34. Use the PivotTable command on the Insert tab to insert a PivotTable in a new worksheet using the WorkshopParticipants table as the source range.
35. Add the Year 2022 data series found in cells D3:D7 to the chart.
36. Insert a Clustered Column PivotChart in the current worksheet.
37. In the PivotTable, change the number format of the Average of 2021 field to the Accounting number format with zero decimal places.
38. Apply Layout 4 to the PivotChart (Quick Layout gallery, second row, first option).
39. Move the slicer in the worksheet so that the upper-left corner is in cell D6.
40. Change the PivotChart type to a Stacked Column chart.
41. Insert a 2-D Line chart in the worksheet based on range A3:F8.
42. Filter the Participants table to display only records with a value of Atlanta in the City field.
43. Use the State slicer to filter the PivotTable to display only data for Arizona and Georgia.
44. Add a slicer to the Workshop Participants PivotTable to filter the data by State.
45. In cell J3, create a formula using the OFFSET function that returns the value in row 2 and column 6 from the starting point of cell A5.
46. In range D5:D12, consolidate data from range D5:D12 in the Spring and Fall worksheets using the SUM function.
47. Add a timeline slicer to the PivotTable based on the Date field in the SpringWorkshops table.
48. Starting in cell A4 in the PivotTable, Drill Down one level to display data for City categories, then expand the Athens label to show the data outline. Drill Down another level to display data for Zip Codes, then Drill Up two levels.
49. Refresh the data in the Workshop Participants PivotTable located in the PivotTable worksheet.