I need help with a step by step with the Excel project

Steps :

Open the start file:EX2019-MOS-Ch4. If the file opens in Protected View, click theEnable Editingbutton in the Message Bar at the top of the file so you can modify it.

The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.

1. On theSales Associatesworksheet, combine the sales associates' first and last names into a single string.

On theSales Associatesworksheet, enter a formula in cellC2using theCONCATfunction to combine the first name from cellB2with the last name from cellA2. Be sure to include a space between the first name and last name. Use cell references as appropriate.

Copy the formula to cellsC3:C9.

On theSales Associatesworksheet, enter a formula in cellC11to count the number of sales associates. (Count the number of names in theFull Namecolumn, cellsC2:C9).

On theSales Associatesworksheet, enter a formula in cellC12to count the number of missing IDs (blank cells) in theSales Associate's IDcolumn (cellsD2:D9).

1. All sales associates' IDs should be ten characters long. Use a formula to identify number of characters in each ID.

On theSales Associatesworksheet, enter a formula in cellE2to calculate the number of characters in the sales associate's ID in cellD2. Use cell references where appropriate.

Copy the formula to cellsE3:E9.

1. Identify the sales region for each sales associate.

On theSales Associatesworksheet, enter a formula in cellF2to find the sales associate's region by extracting the first three characters of the sales associate's ID in cellD2.Use cell references where appropriate.

Copy the formula to cellsF3:F9.

1. For the new IDs, the sales associates' names must be in all upper case.

On theSales Associatesworksheet, enter a formula in cellG2to display the last name from cellA2in all upper case letters. Use cell references where appropriate.

Copy the formula to cellsG3:G9.

1. need the text for the new IDs.

On theSales Associatesworksheet, enter a formula in cellH2, enter a formula usingTextJointo create acomma delimitedstring combining the text from cellsG2, and thenD2.Ignore blank cells.Use cell references where appropriate.

Copy the formula to cellsH3:H9.

1. On theSales Dataworksheet, calculate the potential commission for each sales associate.

Switch to theSales Dataworksheet.

In cellC2, calculate the potential commission earned by multiplying the annual sales in cellB2by the commission rate in cellB11. Use absolute, relative, and mixed cell references as appropriate so the formula will copy and paste properly.

Copy the formula to cellsC3:C9.

1. On theSales Dataworksheet, use a formula to determine if each sales associate will receive a commission payment.

In cellD2, enter a formula using the IF function to displayyesif the associate's sales in columnBweregreater than $199,999. If not, then displayno. Hint:Do not include dollar signs or comma separators in numerical values in formulas. Excel interprets commas as function argument separators.

Copy the formula to cellsD3:D9.

1. On theSales Dataworksheet, use a formula to calculate the actual commission for each sales associate.

In cellE2, enter a formula to display the commission value from cellC2only if the value in cellD2isyes. If the value inD2isnot yes, then the value inE2should be0. Use appropriate cell references.

Copy the formula to cellsE3:E9.

1. On theSales Dataworksheet, calculate the total earnings for each sales associate.

In cellG2, calculate the total earnings using aSUMfunction to total the values in cellsE2andF2.

Copy the formula to cellsG3:G9.

1. On theAnalysis sheet, calculate average, minimum, maximum, and total sales. Use the named ranges shown in cellsA9:B11.

Switch to theAnalysisworksheet.

In cellB5, enter a formula to calculate theaverageannual sales for all sales associates. Use the named rangeAnnualSales.

In cellC5, enter a formula to calculate the minimumannual sales for all sales associates. Use the named rangeAnnualSales.

In cellD5, enter a formula to calculate the maximumannual sales for all sales associates. Use the named rangeAnnualSales.

In cellE5, enter a formula to calculate thetotalannual sales for all sales associates. Use the named rangeAnnualSales.

1. On theAnalysis sheet, calculate average, minimum, maximum, and total commissions. Use the named ranges shown in cellsA9:B11.

In cellB6, enter a formula to calculate theaveragecommission paid for all sales associates. Use the named rangeCommissionEarned.

In cellC6, enter a formula to calculate the minimumcommission paid for all sales associates. Use the named rangeCommissionEarned.

In cellD6, enter a formula to calculate the maximumcommission paid for all sales associates. Use the named rangeCommissionEarned.

In cellE6, enter a formula to calculate thetotalcommission paid for all sales associates. Use the named rangeCommissionEarned.

1. On theAnalysis sheet, calculate average, minimum, maximum, and total earnings. Use the named ranges shown in cellsA9:B11.

In cellB7, enter a formula to calculate theaveragetotal earnings for all sales associates. Use the named rangeEarnings.

In cellC7, enter a formula to calculate the minimumtotal earnings for all sales associates. Use the named rangeEarnings.

In cellD7, enter a formula to calculate the maximumtotal earnings for all sales associates. Use the named rangeEarnings.

In cellE7, enter a formula to calculate the total earnings for all sales associates. Use the named rangeEarnings.

1. On theNew Commission Structureworksheet, use a mixed reference formula to calculate potential commissions.

Switch to theNew Commission Structureworksheet.

On theNew Commission Structureworksheet, enter a formula in cellC3to calculate the commission paid for 10 units multiplied by a 2% commission. Use cell referencesC2andB3. Be very careful with the cell reference type so you can copy the formula and have it calculate correctly across the entire table.

Copy the formula to fill out the table through cellL6.

On theNew Commission Structureworksheet, enter a formula using a counting function in cellD8to calculate the number of commission options in cellsC3:L6. The formula should count all of the numbers.

Save and close the file.

Upload and save your project file.

Submit project for grading.