YO$22\_$Excel$\_$BU$03\_$Assessment$1\_$Fitness

$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$

Total Points

$67$