on fomulas and name manager i miss same named ranges ( Yoga_I, criteria) please i need your help.

BEFOR, GOING TO STEP \#9 Go to Fomulas and Name Manager, Make sure your Named Ranges are like the SCREEN SHOT below: Create and apply an Advanced filter so that only records where the student is female and is enrolled in a Yoga class are displayed. Select A14:E107, and then name the cell range Enrollment. Copy range A14:E14, 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 Create database functions to summarize the data in the Enrojment database. Refer to cell B5 for the field argument in the functions: = CONTA ( B)2: H/4) In cell B7, create a DCOUNT function to calculate the number of values in the fee field of the Enrollment database that meet the fitter criteria specified in the range A1:E2. SEE SCREEN SHOT BELOW TO HELP WITH ALL FUNCTIONS: =AVERAGA Function Argumenss DCOUNT Criteris in the raspe of cell that contans the coedtions you specty. The range indude. and ont cell below the label for a condenciae. Project Description: The hotel has started faciltating fitness classes for hotel goests. Guosts can rogister for ciasses before their arrival or when they arrivo at the hotel. Several instructors lead the classes, and each instructor has multiple skills. The fitness center manager would Ike to have a workbook doveloped that will track dass enrolment. The workbook will provide an overview of guest enrollment with some analysis. which will be used for better decision-making for future class offerngs. Steps to Perform: Step Instructions Pointa Possible Start Excol. Downloed and open the file named Excel_BU03_Assesmenti_Fingess xisx. Grader has 0 aulomatically added your last name to the beginning of the filename. Save fhe fie to the location where you are storing your fies. On the Daa worksheet, soloct the range B4. HB. Create a Classinfo named range. 2 Solect the range B4:H4. Create a Class ibe named range. 2 Select the range 811. H14. Create naened ranges using the top row as the name for your named ranges. Each 2 tange is the list of instructors that can leach individual classes. Select the range B11:H11 and creabe a ClassNames named range for all of the diflerent faness clastes offored at the hotel. On the Enrollment werksheet, in cell A14, insert a takle using range A14.D107 and the sop row asthe heade: in coll D15, enaer an HLOOKUP funeticn that will look up the class ID in cell 815 and will use the Classinto table_array to return the Fee from the fath row of the table. SEE SCREEN SHOT BELOW Futstion Adgurenta H.acue rime int itathethin 2 8 BEFORE GOING TO STEP H9 Go to Fomulas and Name Manager. Make sure your Named Ranges are like the SCREEN SHOT below: 9 Crvate and apply an Advanced filter so that only records where the student is female and is enrolled in a Yoga class are displayed. Select A14: E107, and then name the cell range Enrollment. Copy range A14:E14, and then paste in range A1:E1 to set up the advanced fitter criteria area. Enter the following filter citeria. In cell C2, type F In cell E2, type Yoga 11 On the Report worksheet, create calculations that wil help holet employees manage the fitness ciass 12 enrollments. The user will put an ' x ' in range E4:E10, indicating which class to report on and an ' x ' in range H4:H5 if employees want a report on a specific gender. In cell A4, use a MATCH function nested in an INDEX function to retrieve the Class that whs selected in E4:E10. The MATCH should find the fow where the ' x ' is located and would be used within the INDEX to pull the associated Class value trom the same row within range F4:F10. In ceil B4, use a MATCH tunction nested in an INDEX function to retrieve the Gender that was selecled in H4:H5, 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 trat was selected in H4. H5. Nest the MATCH and INDEX formula inside the IFERROR function in case the user does not select a speofic gender, The IFERROR should lesre the ceil blank. if a gender is not selected. =IFERROR(INDEX(I4:I5,MATCH("xn,H4:H5,0)),") 12. In cell B7, create a HLOCKUP formils that will look up the Class in A4 within the Classinto named range and retum the maxmum enrolment, which is in the third roe of that table. In cel B8, create a HLOOKUP formula that wil use the Class in A4 within the Classinfo named range and ceturn the Class Category, which is in the secend row of that table. 13 In cell B11, use the IFS tunction to indicate the avalabaliy of spots in the selected finess class. If the fumber enroled in CA is greater than the maximam enrolmest in B7, then Overbooked should display. If the number enrolled CA is equal to the maximum entollment in B ?, then Full should dsplay, otherwise, 5 pots Available should display. SEE SCREEN SHOT BELOW =IFS(CA>B7, "Overbooked",C4=B7,"Full", True, "Spots Avaifable") 14. The instructors for each class are listed oe the Dita worksheet in range B12. H14. The instructors for the class in cell A4 need lo be counted. In col B12, create a complex function that wil detemine the number of instructers for the class listed in A4. Use the COUNTA. INDTRECT, INDEX, and MATCH functions. Use the Class_IDs named range inslde the MATCH function and the ClassNames named range inside the INDEX function. ICOUNTAUNORECTONDEXClassNames, MATCH(A4,Class_lds,)10 15 Clok cell B13. Using an HLOOKUP nested in an AND tunction nested in an IF function, retum ether Split Class or Can't Split based on business options. Two condions are needed to delemine it a class can be split Ueing the Classinfo tablo. one row shows if ar class can be splt. That condition can be delomined with a HL.OOKUP. The second is if there is more than one instructor as shown in cel B12. If both conditions are met, the class can be spit. Otherwise, the class cannot be split =IF(AND(HLOOKUP(A4,Classinfo,4FALSE)="'F,BI2O1), "Split Class", "Can't Split") 16. Using the table on the Enroliment worksheet, insert a PivolTable on a new worksheet named PivotAnalysis. Complele the following to creale your Phot fable. + Add the Fee, Gender, and Class Narne felds to the Pivotfable. - Ensure Class name is in the Rows acea. - Move Gender io the Columns area. + in cell B3, replace Calumn Labols with Gender. In cell B4, replace. F with Female. In cel C4, replace M with Male. In cel A3, replace Sum of Fee with Revenue. In cell A4, replace Row Labels with Classes. - Fommat the Revenue field as Acsounting with 0 decimal places. - Resize the colkun withs as needed. - Apply Light Orange. Prot Style Light 10 to the Prottable 17 Insert ar Clustered Column PivotChart on the Pinglanalysis worksheet. 6 Customize the PivolChart as follows. - Repositice the PivotChart so its top ieft corner is in cell F3. Drag the bottom night comer so the PivotChart fats colume N. - Change the color of the PivorChart to Monocheomatic Palene 9. - Apply Style 8 to the Pirobchart. - Add a chart bile above the chart. Replace Chart Tisle with Revenue by Class and Cender BEFOR, GOING TO STEP \#9 Go to Fomulas and Name Manager, Make sure your Named Ranges are like the SCREEN SHOT below: Create and apply an Advanced filter so that only records where the student is female and is enrolled in a Yoga class are displayed. Select A14:E107, and then name the cell range Enrollment. Copy range A14:E14, 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 Create database functions to summarize the data in the Enrojment database. Refer to cell B5 for the field argument in the functions: = CONTA ( B)2: H/4) In cell B7, create a DCOUNT function to calculate the number of values in the fee field of the Enrollment database that meet the fitter criteria specified in the range A1:E2. SEE SCREEN SHOT BELOW TO HELP WITH ALL FUNCTIONS: =AVERAGA Function Argumenss DCOUNT Criteris in the raspe of cell that contans the coedtions you specty. The range indude. and ont cell below the label for a condenciae. Project Description: The hotel has started faciltating fitness classes for hotel goests. Guosts can rogister for ciasses before their arrival or when they arrivo at the hotel. Several instructors lead the classes, and each instructor has multiple skills. The fitness center manager would Ike to have a workbook doveloped that will track dass enrolment. The workbook will provide an overview of guest enrollment with some analysis. which will be used for better decision-making for future class offerngs. Steps to Perform: Step Instructions Pointa Possible Start Excol. Downloed and open the file named Excel_BU03_Assesmenti_Fingess xisx. Grader has 0 aulomatically added your last name to the beginning of the filename. Save fhe fie to the location where you are storing your fies. On the Daa worksheet, soloct the range B4. HB. Create a Classinfo named range. 2 Solect the range B4:H4. Create a Class ibe named range. 2 Select the range 811. H14. Create naened ranges using the top row as the name for your named ranges. Each 2 tange is the list of instructors that can leach individual classes. Select the range B11:H11 and creabe a ClassNames named range for all of the diflerent faness clastes offored at the hotel. On the Enrollment werksheet, in cell A14, insert a takle using range A14.D107 and the sop row asthe heade: in coll D15, enaer an HLOOKUP funeticn that will look up the class ID in cell 815 and will use the Classinto table_array to return the Fee from the fath row of the table. SEE SCREEN SHOT BELOW Futstion Adgurenta H.acue rime int itathethin 2 8 BEFORE GOING TO STEP H9 Go to Fomulas and Name Manager. Make sure your Named Ranges are like the SCREEN SHOT below: 9 Crvate and apply an Advanced filter so that only records where the student is female and is enrolled in a Yoga class are displayed. Select A14: E107, and then name the cell range Enrollment. Copy range A14:E14, and then paste in range A1:E1 to set up the advanced fitter criteria area. Enter the following filter citeria. In cell C2, type F In cell E2, type Yoga 11 On the Report worksheet, create calculations that wil help holet employees manage the fitness ciass 12 enrollments. The user will put an ' x ' in range E4:E10, indicating which class to report on and an ' x ' in range H4:H5 if employees want a report on a specific gender. In cell A4, use a MATCH function nested in an INDEX function to retrieve the Class that whs selected in E4:E10. The MATCH should find the fow where the ' x ' is located and would be used within the INDEX to pull the associated Class value trom the same row within range F4:F10. In ceil B4, use a MATCH tunction nested in an INDEX function to retrieve the Gender that was selecled in H4:H5, 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 trat was selected in H4. H5. Nest the MATCH and INDEX formula inside the IFERROR function in case the user does not select a speofic gender, The IFERROR should lesre the ceil blank. if a gender is not selected. =IFERROR(INDEX(I4:I5,MATCH("xn,H4:H5,0)),") 12. In cell B7, create a HLOCKUP formils that will look up the Class in A4 within the Classinto named range and retum the maxmum enrolment, which is in the third roe of that table. In cel B8, create a HLOOKUP formula that wil use the Class in A4 within the Classinfo named range and ceturn the Class Category, which is in the secend row of that table. 13 In cell B11, use the IFS tunction to indicate the avalabaliy of spots in the selected finess class. If the fumber enroled in CA is greater than the maximam enrolmest in B7, then Overbooked should display. If the number enrolled CA is equal to the maximum entollment in B ?, then Full should dsplay, otherwise, 5 pots Available should display. SEE SCREEN SHOT BELOW =IFS(CA>B7, "Overbooked",C4=B7,"Full", True, "Spots Avaifable") 14. The instructors for each class are listed oe the Dita worksheet in range B12. H14. The instructors for the class in cell A4 need lo be counted. In col B12, create a complex function that wil detemine the number of instructers for the class listed in A4. Use the COUNTA. INDTRECT, INDEX, and MATCH functions. Use the Class_IDs named range inslde the MATCH function and the ClassNames named range inside the INDEX function. ICOUNTAUNORECTONDEXClassNames, MATCH(A4,Class_lds,)10 15 Clok cell B13. Using an HLOOKUP nested in an AND tunction nested in an IF function, retum ether Split Class or Can't Split based on business options. Two condions are needed to delemine it a class can be split Ueing the Classinfo tablo. one row shows if ar class can be splt. That condition can be delomined with a HL.OOKUP. The second is if there is more than one instructor as shown in cel B12. If both conditions are met, the class can be spit. Otherwise, the class cannot be split =IF(AND(HLOOKUP(A4,Classinfo,4FALSE)="'F,BI2O1), "Split Class", "Can't Split") 16. Using the table on the Enroliment worksheet, insert a PivolTable on a new worksheet named PivotAnalysis. Complele the following to creale your Phot fable. + Add the Fee, Gender, and Class Narne felds to the Pivotfable. - Ensure Class name is in the Rows acea. - Move Gender io the Columns area. + in cell B3, replace Calumn Labols with Gender. In cell B4, replace. F with Female. In cel C4, replace M with Male. In cel A3, replace Sum of Fee with Revenue. In cell A4, replace Row Labels with Classes. - Fommat the Revenue field as Acsounting with 0 decimal places. - Resize the colkun withs as needed. - Apply Light Orange. Prot Style Light 10 to the Prottable 17 Insert ar Clustered Column PivotChart on the Pinglanalysis worksheet. 6 Customize the PivolChart as follows. - Repositice the PivotChart so its top ieft corner is in cell F3. Drag the bottom night comer so the PivotChart fats colume N. - Change the color of the PivorChart to Monocheomatic Palene 9. - Apply Style 8 to the Pirobchart. - Add a chart bile above the chart. Replace Chart Tisle with Revenue by Class and Cender