Please complete steps g and h. work is related to excel. Previous steps are listed if needed to understand g and h. A formula is needed for cell m11 on client registration worksheet and a formula for d4 on event analysis.
g. In cell M11, enter a formula that will calculate the total amount due after discounts have been applied. Incorporate an IFERROR function to return a blank value if the formula returns an error. Format the result as Accounting and copy the formula down through M34. h. On the EventAnalysis worksheet, in cell D4, enter a formula that will return Goal_Met if the number of registrants for the event meets or exceeds the goal for that event outlined in the cell range G4:H9, otherwise return Promote_Event Copy the formula down through D9. i. First Resource offers a bonus to the coordinator for each event. The bonus amount is based on the event type and the number of registrants. In addition, there are different amounts awarded if the registration goal has been met or not. Create a Goal_Met named range for cells H14:L17 and a Promote_Event named range for cells H21:L24. i. In cell E4 enter an INDEX function that incorporates the INDIRECT function for the array argument to retrieve the bonus amount the event coordinator will receive. Format the result as Accounting and copy the formula down through E9. Client Rezistrations Event Analysis b. On the EventDetails worksheet, create an EventDetails named range for cells A4:D9. c. On the ClientRegistration worksheet, in cell I11, enter a VLOOKUP function to retrieve the registration fee from the EventDetails named range for the EventID in cell G11. Multiply the value retrieved by the number of guests in cell H11. Incorporate an IFERROR function to return a blank value if the VLOOKUP function returns an error. Format the value as Accounting and copy the formula down through I34. d. Attendees who are also board members receive a discount off the registration fees. The discount amount varies based on the type of event. In cell J11 enter a formula that will return the discount amount if the registrant is a board member and return a blank value (ii") if the registrant is not. - Use a VLOOKUP function to retrieve the EventType from the EventDetails named range. - Use an XLOOKUP function, with the result of the VLOOKUP as the lookup value, the range B5:B8 as the lookup_array, and the range A5:A8 as the return_array. e. First Resource is offering a 5% discount off registration fees for all Event Types except for Survey/Study. In cell K11, enter a formula that will return 0.05 if the event is NOT a Survey/Study, otherwise return a blank value ("I") - Use a VLOOKUP function to return the Event Type from the EventDetails named range. - Use a NOT function so that any event type other than Survey/Study will return TRUE. - Use an IF function to return 0.05 for the value_if true and "" for the value if false. - Incorporate an IFERROR function to return a blank value if the VLOOKUP function returns an error. - Format the result as Percentage with 0 decimal places. - Copy the formula down through K34. f. First Resource also offers a 3% discount off registration fees if the registrant meets certain criteria. In cell L11, enter an IF function that will return 0.03 if the following criteria are met, otherwise return a blank value ("'")
