Hello I need help with this assignment$$YO$22\_$Excel$\_$BU$03\_$Assessment$1\_$Fitness

$$

Project Description:

The hotel has started facilitating fitness classes for hotel guests. Guests can register for classes before their arrival or when they arrive at the hotel. 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.

$$

Steps to Perform:

Step

Instructions

Points Possible

$1$

Start Excel. Download and open the file named Excel$\_$BU$03\_$Assessment$1\_$Fitness.xlsx$.$ Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.

$0$

$2$

On the Data worksheet, create a ClassInfo named range for the cell range B$4$:H$8.$

$2$

$3$

Create a Class$\_$IDs named range for the cell range B$4$:H$4.$

$2$

$4$

Select the range B$11$:H$14.$ Create 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.

$2$

$5$

Select the range B$11$:H$11$ and create a ClassNames named range for all of the different fitness classes offered at the hotel.

$2$

$6$

On the Enrollment worksheet, in cell D$15,$ enter an HLOOKUP function that will look up the class ID in cell B$15$ and will use the ClassInfo table$\_$array to return the Fee from the fifth row of the table. Copy the formula down through cell D$107$

$8$

$7$

In cell E$14,$ create a new column header named Class$\_$Name.

In cell E$15,$ enter an HLOOKUP function that will look up the class number in cell B$15$ and use the ClassInfo table to return the Class Category name from the second row of the table with an exact match. Copy the formula down through cell E$107$

$8$

$8$

On the Report worksheet, create calculations that will help hotel employees manage the fitness class enrollments. The user will put an $$x$$ in range E$4$:E$10,$ indicating which class to report on and an $$x$$ in range H$4$:H$5$ if employees want a report on a specific gender.

In cell A$4,$ use the XLOOKUP function to retrieve the class that was selected with an $"$x$"$ in the range E$4$:E$10.$ If no class is selected, have the function return the text No Class Selected

In cell B$4,$ use a MATCH function nested in an INDEX function to retrieve the Gender that was selected in H$4$:H$5,$ looking at the $$x$$ in column H and returning the $$F$$ or $$M$$ for the Gender criteria. Using a MATCH nested in an INDEX function, retrieve the gender that was selected in H$4$:H$5.$ Nest the MATCH and INDEX formula inside the IFERROR function in case the user does not select a specific gender. The IFERROR should leave the cell blank if a gender is not selected.

$10$

$9$

In cell B$7,$ create a HLOOKUP formula that will look up the Class in A$4$ within the ClassInfo named range and return the maximum enrollment, which is in the third row of that table.

In cell B$8,$ create a HLOOKUP formula that will use the Class in A$4$ within the ClassInfo named range and return the Class Category, which is in the second row of that table.

$9$

$10$

In cell B$11,$ use the IFS function to indicate the availability of spots in the selected fitness class. If the number enrolled in C$4$ is greater than the maximum enrollment in B$7,$ then Overbooked should display. If the number enrolled C$4$ is equal to the maximum enrollment in B$7,$ then Full should display, otherwise, Spots Available should display.

$8$

$11$

The instructors for each class are listed on the Data worksheet in range B$12$:H$14.$ The instructors for the class in cell A$4$ need to be counted.

In cell B$12,$ create a complex function that will determine the number of instructors for the class listed in A$4.$ Use the COUNTA, INDIRECT, INDEX, and MATCH functions. Use the Class$\_$IDs named range inside the MATCH function and the ClassNames named range inside the INDEX function.

$8$

$12$

Click cell B$13.$ Using an HLOOKUP nested in an AND function nested in an IF function, return either Split Class or Can$$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 is if there is more than one instructor as shown in cell B$12.$ If both conditions are met, the class can be split. Otherwise, the class cannot be split.

$8$

$13$

Save and close Excel$\_$BU$03\_$Assessment$1\_$Fitness. Exit Excel. Submit the file as directed.

$0$