lluslroled Excel EDI a | Module 3: EM Project llo 1.0. 1.1. a. Enter the test No Into cell 15. b. Uslng the range 14:15 as the crlterla, create an

Excel 2016 | Module 8: SAM Project 1b

Hi! Can someone please help me with this excel project. Thank you! attached is the problem and excel starter sheet.

• Attachment 1
• Attachment 2
• Attachment 3
• Attachment 4
• Attachment 5
• Attachment 6
• Attachment 7
• Attachment 8
• Attachment 9

lluslroled Excel EDI a | Module 3: EM Project llo 1.0.1.1. a. Enter the test No Into cell 15. b. Uslng the range 14:15 as the crlterla, create an advanced fllter thatestracls records from the table In the range A4:L‘334 to the range beglnnlng at cell 1?. (Hint: You wlll need to swltch the actlon In theAdvanced Fllter dlalog box to the “Copy to another locatlon“ ootlon.) Josh has created an area In the workbook Where cllnlc lnformatlon can be easlh.rretrleved. He aslts you to enter I‘unctlons to help wlth the data retrleval. Swltch to the Eiinics worksheet. In cell 33, enter a formula uslng the VLDDKUPfunctlon to dlsplay the number of students enrolled In the cllhlc wlth the ID at code Ilsted In cell .15 as follows: a. Use cell 15 as the Iool-tup value. b. Use Tabla: (table on the Ciinics worksheet) as the table array. c. The functlon should return the value In column 1" of the table array.d. Use FflLSE as the Ftange_lool-c:up argument. In cell L3, enter a formula uslng the DSUH functlon to calculate the totalpayments correspondlng to the gender Ilsted In cell L5 as follows: a. Use Tahla2[#illl] [the entlre table on the Eiinics worksheet, Includlngthe header row} as the formula database. b. Use the Payments fleld header [In cell H4) as the fleld argument.c. Use the range L4:L5 as the crlterla argument. In cell NB. enter a formula uslng the DEDUHTA functlon to count the numberof cllnlcs on the Eiloics worksheet wlth fewer than 10 members enrolled as follows: a. Use Tahla2[#illl] [the entlre table on the Eiinics worksheet, Includlngthe header row} as the formula database. b. Use the ID alt fleld header [In cell A4} as the fleld argument.c. Use the range H4:N5 as the crlterla argument.Josh asl-ts you for a Ilst of cllnlcs that are not at capaclty organlzed by sesslon. Swltch to the (loan Eiioics worksheet. Use a number fllter In the Enrolledcolumn to dlsolay.r cllnlcs wlth openlngs {:10}. [Mac Hlnt: In the fllter dlalog boat, fllter values that are less than 10.] Sort the filtered table by Sesslbn In ascendlng [Smallest to Largest) order[Column D}. Josh asl-ts you for help In gettlng a breakdown of the payments by sesslon. Swltch to the Subtotais I.I.Ir.:url-c.'sheet. Sort the table on the Subtotais worksheetby the Sesslon value [Column D} In ascendlng {Smallest to Largest} order. Convert the table to a range. Add subtotals to the range A4:F43 that. for each change In the Sesslon valueIwlll use the Sum functlon to add subtotals to the Cost fleld. Keep the default settlngs of dlsp-Iaylng the summary below data and replaclng current subtotals.