Start Excel. Download and open the file named Excel$\_$Ch$05\_$PS$1\_$ResourceCenter.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.

It is considered best practice to create a named range for tables of data from which data will be retrieved with retrieval functions.

On the EventDetails worksheet, create an EventDetails named range for cells A$4$:D$9.$

Registration fees, located in the EventDetails table, are based on a particular Event's EventID. Registration fees must be paid for each person attending the event.

On the ClientRegistration worksheet, in cell $111$ enter a VLOOKUP function to retrieve the registration fee from the EventDetails named range for the EventID in cell G$11.$ Multiply the value retrieved by the number of guests in cell H$11.$ 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 $130.$

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 J$11,$ enter a formula that will return the discount amount if the registrant is a board member and return a blank value $("\text{'}{?}^{\text{'}})$ if they are not. Use a XLOOKUP function, with the value retrieved by a VLOOKUP using the EventDetails named range as the lookup$\_$value, the range $B5$:$B8$ as the lookup$\_$array, and the range $A5$:$A8$ as the return$\_$array.

Use an IF function so that the discount only applies to board members. Format the result as Percentage with $0$ decimal places. Copy the formula down through J$3$O$.$

First Resource is offering a $5\%$ discount off registration fees for all EventTypes except for Survey$/$Study$.$

In cell K$11,$ enter a formula that will return $0\mathrm{.}05$ if the event is NOT a Survey$/$Study$,$ otherwise return a blank value $(\text{'}"\text{'}).$ Use a VLOOKUP function to return the EventType 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\mathrm{.}05$ for the value$\_$if$\_$true and $\text{'}"\text{'}$ 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 and copy the formula down through K$30.$