I need help with question 4

MODULE 5 WORKSHEET ASSIGNMENT (50 points) Page 1 of 4 Buckeye Gymnastics is sponsoring a Big 10 Gymnastics Meet at their gym in Powell, Ohio. There are four different events at the meet: beam, bars, floor, and vault. Each competitor is also assigned an ability level, which is predetermined by the coach. You have setup the following worksheets in a workbook to help keep track of the statistics generated about each gymnast: Gymnast worksheet - This worksheet gives the input data about each gymnast including their entry number, name, team, class rank, level, hours practiced, number of events, number of medals received, and overall average score received during the meet for the 4 different events. Levels worksheet - A competitor's level determines the recommended number of practice days per week, as well as practice hours per day. For example, a competitor who is in level 5 should practice 5 hours per day, 4 days per week, etc. Note: Valid level numbers are 5-10. The Round of Performance is based on the gymnast's level and the event(s) in which they participated. For example, if a gymnast is assigned a level between 5-6 and is performing on the vault, they will perform in Round One, etc. Events worksheet - This worksheet gives the statistics of each gymnast based on whether they performed in an event or not. It displays the round performed in or if they did not perform in a particular event, a dash "-" is displayed. The scores earned are listed for each event a gymnast performed. A zero score means that the gymnast did not participate in that event. Team Summary worksheet - Each school's team is listed by each gymnast entry number, their total score for all events participated in (All Around Score), how many medals the gymnast won, and the gymnast's name. The All Around Score is a total of the individual's all around average scores on four events. NOTE: Gymnast!A2:H16 is given a named range of MedalsWon for convenience. Please use it when/of needed. Download the Excel start file to begin your work. Notice that this start file only includes the data shaded in gray or data that is given to you in it. You will be inputting proper formula or functions for the remaining data in the unshaded cells by answering the following questions using formula or functions and cell references from this workbook. You will input your formula or functions in the proper worksheet, NOT in this file. Remember to start all formula or functions with an equal (=) sign and to always use cell references where possible. Only use a $ if necessary when copying formula or functions down or across. Also remember to use the worksheet name, then a !, then the cell value wen referencing a cell that is not in the current worksheet. MODULE 5 WORKSHEET ASSIGNMENT (50 points) Page 2 of 4 See questions below ... MODULE 5 WORKSHEET ASSIGNMENT (50 points) Page 3 of 4 1. (0-7 pts) Write one or more Excel function(s) in cell Gymnast!F2, which can be copied down the column, to determine how many hours per week the corresponding gymnast should practice. The hours and days are determined by the level of the gymnast. (Hint: Use two HLOOKUP() functions here, one to lookup the gymnast level (from the E column) and return the time practiced (column 2 of the table array) and one to lookup the gymnast level (from the E column) and return the days per week (column 3 of the table array). The time and days per week are in a table array in the Levels worksheet, range B2:G4. Then multiply these 2 values together. Remember to always check for if a $ is needed for the numbers in any cells used since you are copying these two functions whose results are multiplied together down.). 2. (0-7 pts) Write an Excel function in cell Gymnast!G2, which is to be copied down, to determine the number of events the corresponding gymnast performed in. (Hint: Use the COUNTIF() function here