I need some help with starting my Excel homework

Start Excel. Download and open the file named Excel_Ch05_PS1_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 A4:D9.

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 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.

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 ("") if they are not. Use a VLOOKUP function to retrieve the EventType from the EventDetails named range. Use an INDEX function, with the cell range A5:B8 on the ClientRegistration worksheet as the array and the result of the VLOOKUP as the lookup_value for the MATCH function needed for the row_num argument. Use an IF function so that the discount only applies to board members and all others will display as a blank ("") Format the result as Percentage with 0 decimal places. Copy the formula down through J34.

First Resource is offering a 5% discount off registration fees for all EventTypes 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 (""). 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.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 and copy the formula down through K34.

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 (""). If the registrant is a board member and had 3 or more people in the party OR the registrant has a total of 5 or more in their party. Format the result as Percentage with 0 decimal places and copy the formula down through L34.

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 formula returns an error. Format the result as Accounting and copy the formula down through M34.

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.

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.

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.

Save and close the workbook Excel_Ch05_PS1_ResourceCenter.xlsx. Exit Excel. Submit the file as directed.

Vang_Excel_CHOS_P51_ResourceCenter - Excel A Xee Vang N File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do AutoSum Calibri E - 11 - do Wrap Text General EX - 47 O Paste Cut Copy Format Painter BIU. A Merge & Center Insert Delete Format $ v % % 2 .00 Fill Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find a Filter Select Clipboard Font Alignment Number Cells Editing 111 X fo D E F G H 1 K L M 7 8 9 Registration Fees Board Membe r Discount Event Discount Board Member Friends Discount Amount Due Guests Yes 10 Lastname 11 Calhoun 12 Wallace 13 Washington 3 1 1 2 2 1 1 2 14 Bryan Yes Yes 1 5 Yes Company Name SEA-LECT Plastics Corporation SMT LLC Southern Indiana Plastics Southwest Mold Inc. SPI Industries Inc. Steinwall Inc. Stihl Incorporated Tasus Corporation - Alabama Tasus Corporation - Texas Team 1 Plastics Technical Industries Inc. Thogus Products Company Trademark Plastics Inc. Treemen Industries Vantec Inc. Venture Plastics Wabash Plastics Inc. GTM Plastics Inc. GTR Enterprises Hansen Plastics Corporation Date Registered 11-Jan-191 17-Dec-18 16-Feb-18 09-Feb-19 07-Apr-19 30-Mar-18 23-Jul-19 17-Feb-18 05-May-18 22-Aug-18 01-Apr-18 01-Nov-18 14-Jun-19 10-Mar-18 21-Jul-18 28-Nov-19 24-May-18 01-Jul-18 04-Feb-18 22-Jun-18 EventID EVNT-0037 EVNT 0037 EVNT-0012 EVNT-0037 EVNT-0042 EVNT-0022 EVNT-0022 EVNT-0042 EVNT-0017 EVNT 0022 EVNT-0012 EVNT-0037 EVNT-0037 EVNT-0032 EVNT-0017 EVNT-0022 EVNT-0032 EVNT 0012 EVNT-0032 EVNT-0022 15 Tyson 16 Lowe 17 Shaffer 18 Oneil 19 Conrad 20 Henry 21 21 Gonzales 22 Marquez 23 Hughes 24 Lara 25 Estes P6 Greene 27 Henson 28 Oneil 29 Sullivan 30 Delacruz 31 32 33 1 1 1 1 31 Yes 2 1 1 Yes 3 1 1 Yes 1 3 2 1 Client Registration EventDetails EventAnalysis + Ready H Vang Excel_Ch05_PS1_ResourceCenter Excel A Xee Vang File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share AutoSum Calibri 11 2 Wrap Text Custom IN RA * Cut Be Copy Format Painter Fill Paste BIU Insert Delete Format $ % Merge & Center 60 .00 0 Conditional Format as Cell Formatting Table Styles Clear Sort & Find & Filter Select Clipboard Font Alignment Number Styles Cells Editing A4 X fic EVNT-0037 B D E F G H 1 K L M 1 Event Details 2 3 EventType Registration Fee 1 5 EventID EventName 2019 Benchmarking Conference EVNT-0037 Attendee EVNT-0012 January HR Peer Networking EVNT-0042 Toyota Plant Tour EVNT-0022 Marsh Bellofram Plant Tour EVNT-0017 Human Resource Professionals EVNT-0032 State of the Industry Annual Conference $ Peer Networking $ Tour Plant Tour $ Peer Networking $ Survey/Study $ 120.00 180.00 250.00 190.00 110.00 180.00 7 8 9 0 A 1 2 3 4. 5 6 7 8 9 20 21 22 3 24 25 26 27 ClientRegistration EventDetails EventAnalysis Vang_Excel_Ch05_PS1_ResourceCenter - Excel A Xee Vang File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share AutoSum * Cut E Copy Calibri 11 - ab Wrap Text General f H Insert Delete Format Fill Paste Cell B I U Av Merge & Center $ % 60 ,00 Conditional Format as Formatting Table Styles Sort & Find & Filter Select Format Painter Clear Clipboard Font Alignment Number Styles Cells Editing fic EventID A B D E F G . J K L M N. o O Q RA 1 Event Analysis Action Bonus TotalRegistrants 23 66 EventID EVNT-0037 EVNT-0012 EVNT-0042 EVNT-0022 EVNT-0017 EVNT-0032 EventType Annual Conference Peer Networking Plant Tour Plant Tour Peer Networking Survey/Study Registration Goals EventID Registration Goal EVNT-0037 20 EVNT-0012 70 EVNT-0042 25 EVNT-0022 40 EVNT-0017 20 EVNT-0032 25 27 38 21 24 $ -NMOOEMANSMISS Annual Conference Peer Networking Plant Tour Survey/Study Goal Met 20 25 30 35 40 150.00 $ 170.00 $ 190.00 $ 210.00 $ 230.00 140.00 $ 160.00 $ 180.00 $ 200.00 $ 220.00 160.00 $ 180.00 $ 200.00 $ 220.00 $ 240.00 120.00 $ 140.00 $ 160.00 $ 180.00 $ 200.00 $ $ $ 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Annual Conference Peer Networking Plant Tour Survey/Study $ $ $ $ Promote Event 20 25 30 35 40 100.00 $ 120.00 $ 140.00 $ 160.00 $ 180.00 90.00 $ 110.00 $ 130.00 $ 150.00 $ 170.00 110.00 $ 130.00 $ 150.00 $ 170.00 $ 190.00 70.00 $ 90.00 $ 110.00 $ 130.00 $ 150.00 28 20 ClientRegistration EventDetails EventAnalysis + Vang_Excel_CHOS_P51_ResourceCenter - Excel A Xee Vang N File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do AutoSum Calibri E - 11 - do Wrap Text General EX - 47 O Paste Cut Copy Format Painter BIU. A Merge & Center Insert Delete Format $ v % % 2 .00 Fill Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find a Filter Select Clipboard Font Alignment Number Cells Editing 111 X fo D E F G H 1 K L M 7 8 9 Registration Fees Board Membe r Discount Event Discount Board Member Friends Discount Amount Due Guests Yes 10 Lastname 11 Calhoun 12 Wallace 13 Washington 3 1 1 2 2 1 1 2 14 Bryan Yes Yes 1 5 Yes Company Name SEA-LECT Plastics Corporation SMT LLC Southern Indiana Plastics Southwest Mold Inc. SPI Industries Inc. Steinwall Inc. Stihl Incorporated Tasus Corporation - Alabama Tasus Corporation - Texas Team 1 Plastics Technical Industries Inc. Thogus Products Company Trademark Plastics Inc. Treemen Industries Vantec Inc. Venture Plastics Wabash Plastics Inc. GTM Plastics Inc. GTR Enterprises Hansen Plastics Corporation Date Registered 11-Jan-191 17-Dec-18 16-Feb-18 09-Feb-19 07-Apr-19 30-Mar-18 23-Jul-19 17-Feb-18 05-May-18 22-Aug-18 01-Apr-18 01-Nov-18 14-Jun-19 10-Mar-18 21-Jul-18 28-Nov-19 24-May-18 01-Jul-18 04-Feb-18 22-Jun-18 EventID EVNT-0037 EVNT 0037 EVNT-0012 EVNT-0037 EVNT-0042 EVNT-0022 EVNT-0022 EVNT-0042 EVNT-0017 EVNT 0022 EVNT-0012 EVNT-0037 EVNT-0037 EVNT-0032 EVNT-0017 EVNT-0022 EVNT-0032 EVNT 0012 EVNT-0032 EVNT-0022 15 Tyson 16 Lowe 17 Shaffer 18 Oneil 19 Conrad 20 Henry 21 21 Gonzales 22 Marquez 23 Hughes 24 Lara 25 Estes P6 Greene 27 Henson 28 Oneil 29 Sullivan 30 Delacruz 31 32 33 1 1 1 1 31 Yes 2 1 1 Yes 3 1 1 Yes 1 3 2 1 Client Registration EventDetails EventAnalysis + Ready H Vang Excel_Ch05_PS1_ResourceCenter Excel A Xee Vang File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share AutoSum Calibri 11 2 Wrap Text Custom IN RA * Cut Be Copy Format Painter Fill Paste BIU Insert Delete Format $ % Merge & Center 60 .00 0 Conditional Format as Cell Formatting Table Styles Clear Sort & Find & Filter Select Clipboard Font Alignment Number Styles Cells Editing A4 X fic EVNT-0037 B D E F G H 1 K L M 1 Event Details 2 3 EventType Registration Fee 1 5 EventID EventName 2019 Benchmarking Conference EVNT-0037 Attendee EVNT-0012 January HR Peer Networking EVNT-0042 Toyota Plant Tour EVNT-0022 Marsh Bellofram Plant Tour EVNT-0017 Human Resource Professionals EVNT-0032 State of the Industry Annual Conference $ Peer Networking $ Tour Plant Tour $ Peer Networking $ Survey/Study $ 120.00 180.00 250.00 190.00 110.00 180.00 7 8 9 0 A 1 2 3 4. 5 6 7 8 9 20 21 22 3 24 25 26 27 ClientRegistration EventDetails EventAnalysis Vang_Excel_Ch05_PS1_ResourceCenter - Excel A Xee Vang File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share AutoSum * Cut E Copy Calibri 11 - ab Wrap Text General f H Insert Delete Format Fill Paste Cell B I U Av Merge & Center $ % 60 ,00 Conditional Format as Formatting Table Styles Sort & Find & Filter Select Format Painter Clear Clipboard Font Alignment Number Styles Cells Editing fic EventID A B D E F G . J K L M N. o O Q RA 1 Event Analysis Action Bonus TotalRegistrants 23 66 EventID EVNT-0037 EVNT-0012 EVNT-0042 EVNT-0022 EVNT-0017 EVNT-0032 EventType Annual Conference Peer Networking Plant Tour Plant Tour Peer Networking Survey/Study Registration Goals EventID Registration Goal EVNT-0037 20 EVNT-0012 70 EVNT-0042 25 EVNT-0022 40 EVNT-0017 20 EVNT-0032 25 27 38 21 24 $ -NMOOEMANSMISS Annual Conference Peer Networking Plant Tour Survey/Study Goal Met 20 25 30 35 40 150.00 $ 170.00 $ 190.00 $ 210.00 $ 230.00 140.00 $ 160.00 $ 180.00 $ 200.00 $ 220.00 160.00 $ 180.00 $ 200.00 $ 220.00 $ 240.00 120.00 $ 140.00 $ 160.00 $ 180.00 $ 200.00 $ $ $ 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Annual Conference Peer Networking Plant Tour Survey/Study $ $ $ $ Promote Event 20 25 30 35 40 100.00 $ 120.00 $ 140.00 $ 160.00 $ 180.00 90.00 $ 110.00 $ 130.00 $ 150.00 $ 170.00 110.00 $ 130.00 $ 150.00 $ 170.00 $ 190.00 70.00 $ 90.00 $ 110.00 $ 130.00 $ 150.00 28 20 ClientRegistration EventDetails EventAnalysis +