The college has started facilitating fitness classes for student, faculty and staff. People can register for classes using an app on the myPGCC portal. Several instructors lead the classes, and each instructor has multiple skills. The fitness center manager would like to have a workbook developed that will track class enrollment. The workbook will provide an overview of guest enrollment with some analysis, which will be used for better decision-making for future class offerings.

tions (2) Protected ViewSaved to this PC -files from the internet can contain viruses. Unless you need to edit, it s safer to stay in Protected View. words 18 INT 2140 Step 1 Project 3 Part 2- Fitness Project Description: The college has started facilitating fitness classes for student, faculty and staff. People can register for classes using an app on the myPGCC portal. Several instructors lead the classes, and each instructor has multiple skits. The fitness center manager would like to have a workbook developed that will track class enrollment. The workbook will provide an overview of guest enrollment with some analysis, which will be used for better decision-making for future class offerings Steps to Perform: 2 3 4 5 Enable Editing Instructions Start Excel Open the downloaded Excel file named P2P2 Fitness xlsx Save the file with the name P2P2 Fitness LastFirst replacing LastFirst with your last and first name Copy the Enrollment worksheet to the end of the workbook to make a backup copy of the data Rename the worksheet EnrollmentBackup On the InputData worksheet, select range B4H8 Create named ranges using the top row as the name for your named ranges. Each range includes fitness class details, With B4 HB still selected, enter Classinfo into the Name Box to name the table Look at range B11 H14. Create 7 named ranges using the top row as the name for your named ranges. Each range is the list of instructors that can teach individual classes P On the Enrollment worksheet, in cell A13, insert a table using range A13 D106 and the top row as the headers Sat Be Franza Danas.command.co.that.cous 1hrough, 13ara lockar in placeabile.scsodina. angelwashington111@outlock.com Summer 2020 Points Possible 0 1 4 4 2 For tructions 2 Piotected vie-Seved to this PC areful-tiles from the Internet can contain viruses. Unless you need to edit, it s safer to stay in Protected View. 2 6 7 8 9 10 Enable Editing Set the Frenze Panes command so that rows 1 through 13 are locked in place while scrolling through the Enrollment worksheet In cell D14, enter an HLOOKUP function that will look up the class number in cell B14 and will use the Classinfo table to return the Fee from the fifth row of the table with an exact match in cell E13, create a new column header named Class Nome in cell E14, enter an HLOOKUP function that will look up the dass number in cell B14 and use the Classinfo table to return the Class Category name from the second row of the table with an exact match Select A13 E106, and then name the cell range Enrollment. Create 5 named ranges using the top row as the name for the named ranges. Copy range A13 E13, and then paste in range A1:E1 to set up the advanced fiter criteria area Enter the following filter criteria In cell C2, type F In cell E2, type Yoga (This item is designed to stretch your knowledge) in cell B6, create a COUNTA function to count the number of enrolled students. in cell B7, create a AVERAGE function to average the Fee field in the Enrollment table In cell B3, create a SUM function to sum the Fee field in the Enrollment table angelin ashington11100utik.com 5 5 5 5 Fo 122 (2) ^ Page Proje Part_Fitness_Instructions (2) Protected ViewSaved to this PC- ED VIEW Be careful-files from the internet can contain viruses. Unless you need to edit, it s safer to stay in Protected View. 41 of bad wurdy 10 Step 11 12 13 14 15 Instructions Enable Editing In cell C6 determine the total number of males currently enrolled Copy this function to cell 06 to determine the total number of females currently enrolled in cell C7 determine the average fee for males. Copy this function to cell D7 to determine the average fee for females In cell CB determine the total fee form males. Copy this function to cell DB to determine the total fee for females. Format the numbers in the range 86 D8 appropriately. On the Report worksheet in cell C4, create a COUNTIF formula that counts the enrollment for the named range Class that has the class number listed in A4. The range criteria should reference the Class named range Still on the Report worksheet in cell 87, create an HLOOKUP formula that will look up the Class in A4 within the Classindo named range and return the maximum enrollment, which is in the third row of that table. The value should be looking for an exact match in cell 88, create an HLOOKUP formula that will use the Class in A4 and retrieve the Class Category type from the Classinfo named range in tow 2, also looking for an exact match In cell B11, create an IF function to indicate the availability of reservations. If the number enrolled in C4 is greater than or equal to the maximum enrollment in 87, then Full or Overbooked should display. Otherwise, Spots Available should display Click cell 812 Using an HLOOKUP nested in an AND function nested in an IF function, return ether Spit Class or Don t Split based on business options. Two conditions are needed to determine if a class can be spit. Using the Classinfo table, one row shows a class can be split. That condition can be determined with a HLOOKUP The second condition is if there is the Availability is Full or Overloaded as shown in cell B11. If both conditions are met the class can be split Otherwise, the class cannot be spit 16 Ensure that the worksheets are correctly named and placed in the following order in the workbook Enrolment InputData Report EnrollmentBackup Save the workbook Clone the angeleshington 11100 Points Possible 10 5 5 10 0 Focus ul-files from the internet can contain viruses. Unless you need to edit, it s safer to stay in Protected View. Enable Editing 5 3 On the Report worksheet in cell C4, create a COUNTIF formula that counts the enrollment for the named range Class that has the class number listed in A4. The range criteria should reference the Class named range. 12 13 14 15 16 Still on the Report worksheet in cell B7, create an HLOOKUP formula that will look up the Class in A4 within the Classinfo named range and return the maximum enrollment, which is in the third row of that table. The value should be looking for an exact match. In cell B8, create an HLOOKUP formula that will use the Class in A4 and retrieve the Class Category type from the Classinfo named range in row 2, also looking for an exact match. In cell B11, create an IF function to indicate the availability of reservations. If the number enrolled in C4 is greater than or equal to the maximum enrollment in 87, then Full or Overbooked should display. Otherwise, Spots Available should display. Click cell B12. Using an HLOOKUP nested in an AND function nested in an IF function, return i either Split Class or Don t Split based on business options. Two conditions are needed to determine if a class can be split. Using the Classinfo table, one row shows if a class can be split. That condition can be determined with a HLOOKUP. The second condition is if there is the Availability is Full or Overloaded as shown in cell B11. If both conditions are met, the class. can be split. Otherwise, the class cannot be split. Ensure that the worksheets are correctly named and placed in the following order in the workbook Enrollment, InputData, Report, EnrollmentBackup. Save the workbook Close the workbook and then exit Excel Submit the workbook as directed Total Points. 8 5 5 10 0 75