Description

In cells B22:M22, use an appropriate function to calculate the average attendance for each month. 18. In cells B23:M23, use an appropriate function to calculate the average revenue for each month. 19. In cells B24:M24, use an appropriate function to calculate the maximum revenue for each month. 20. In cells B25:M25, an appropriate function to calculate the minimum revenue for each month. 21. Use conditional formatting to show the month with the highest average attendance; the formatting can be your choice. 22. The center needs to bring in an average of $13,676 a month to stay on budget. In cell B26, use the appropriate function to check whether the value of cell B23 is greater than or equal to 13,676. a. If this condition is true, the cell value should be set to Over b. If this condition is false, the cell value should be set to Under c. Copy the formula created in cell B26 to the range C26:M26. Page 3 of 4 23. The center may want to cancel some classes. In range N9:N13 use the appropriate function to determine the total amount of attendance for each class. 24. Then, in cell O9 create a nested IF function that returns Cancel for attendance less than 900 and Awesome for classes that are greather than 1000, otherwise the return should be OK and for all other classes. 25. In cell B28, create a Line Sparkline from the data in the range G11:K11, and then change the style of the Sparkline to Turquoise, Sparkline Style Accent 3 (Dark Gray or no Dark/Light) - Depending on your version of Office, the sparkline style may be written as Sparkline Style Accent 3 (no dark or light) 26. In cell B30, use an appropriate function that counts the number of classes that are Over the Revenue Rank 27. In cell B31, use an appropriate function that counts the number of classes that are Under the Revenue Rank 28. Check the Spelling in the workbook to identify and correct any spelling errors. (Hint: You should find and correct at least two spelling errors.) 29. Format the worksheet for printing as described below: a. Change the orientation to Landscape. b. Change the worksheet margins to Narrow. c. Insert a header in the center section with the text 2018 Class Statistics. d. Scale the worksheet so that it prints on one page. 30. Stella’s manager would like to emphasize certain visuals in the worksheet. Create a Line chart from the nonadjacent ranges B8:M8 and B20:M20. Move the chart you just created to its own new chart sheet. Use Monthly Revenue Chart as the name of the new chart sheet. 31. On the Monthly Revenue Chart worksheet, format the line chart with the following options: a. Change the chart title to Total Revenue by Month. b. Format the data labels using the Above positioning option. 32. Switch back to the Class Statistics worksheet. Use the Recommended Chart tool to create a Clustered Column chart based on the range A8:F13. Move the chart to its own new chart sheet. Use the name Fall Attendance Chart as the name of the new chart sheet. 33. On the Fall Attendance Chart chart sheet, format the clustered column chart with the following options: a. Change the chart style to Chart Style 2. b. Change the chart title to Fall Semester Class Attendance. c. Add Number of Attendees as the primary vertical axis title. d. Add Fall Semester Months as the primary horizontal axis title. Page 4 of 4 34. Stella’s manager was very impressed with her work and would like her to include and format some other information as well. Go to the Personal Trainers worksheet and make the following formatting changes: a. Rotate the labels in the range B3:B17 to 0 degrees. b. Copy the range C3:C5, then paste it into the range C6:C17. Use the paste option that pastes the values, but not the cell formatting. c. Set the fill color in the range D6:D8 to No Fill. 35. The center uses special codes to keep track of hours and process payments. These codes are made by typing the first three letters of the day followed by a dash, the start and end times separated by a colon, a dash, and then the first two letters of the first and last name of the trainer. Use Flash Fill to fill the values in the range E4:E17 by starting in E3 and typing (in E3): Mon-9:11-NoTh. 36. In the Physical Therapists worksheet, Freeze Panes so that when you scroll you can always see Column A and Rows 1 & 2. 37. Autofit Columns A:I on the Physical Therapists worksheet 

Instruction Files