Function Library

Defined Names

$32.$ Set the print area of the worksheet to the cell range A$1$ through H$33.$

B

c

Available

Open the Assignment $3\_$Document file in Excel. In the File tab, save the file with the name Firstname$\_$Lastname$\_$Assignment$3,$ where $2$ 'Firstname and Lastname are replaced with your first and last names.

Sales Rep Performance worksheet

$2$:$2.$ Insert a column before column $C.$

$2,3.$ In cell C$3,$ enter the text Full Name.

In cell C$4,$ write a formula that concatenates the first name $($cell B$4),$ followed by a space and then the last name $($cell A$4).$ When done icell C$4$ should display the name Bud Fox. Copy the formula in cell C$4$ to the cell range C$5$ through C$12.$ Your formula must use the $4$'appropriate function that you learned about to perform the concatenation.

$2,5.$ Adjust the width of column $C$ to $20.$

Select the appropriate cells for the lookup table and give it the range name SalesRangeLookup. Make sure to select only the appropriate cells for the lookup table that would be used in one of the two lookup functions you learned about, $2$ ias you will be using the named range in the next question.

In cell D$4,$ write a formula that will look up the sales rating for sales rep Bud Fox, based on his total sales in cell $14.$ You must use the inamed range you created in Step $6$ in your formula. Copy the formula to the cell range D$5$ through D$12.$ Hint: You will need to use one of the two lookup functions you learned about in Lesson $7$ in order to complete this step correctly. Based on the format of the lookup table, iyou should be able to identify which function to use. Since Bud has a total sales of over $$1$ million, you should expect his sales rating to $6$ibe WooHoo.

In cell K$4,$ write a formula that will look up the low interest loan entitlement for sales rep Bud Fox, based on his total sales in cell J$4.$ You must use the named range you created in Step $6$ in your formula. Copy the formula to the cell range K$5$ through K$12.$ Hint: You should $2)$ find that your formula is almost identical to the formula you created in Step $7.$

In cell E$4,$ write a formula that determines the employment status of the sales rep Bud Fox $($cell D$4).$ If the sales rating of Bud is equal to Oh$-$Oh$,$ then his employment status is displayed as Terminated, otherwise his employment status is displayed as Employed. Copy the $6$ formula to the cell range $E$ through E$12.$ Hint: You will need to use the IF function in your formula to answer this step correctly.

In cell L$4,$ write a formula to determine the monthly loan payment of sales rep Bud Fox's low interest loan entitlement in cell K$4.$ Make isure your formula displays a positive monthly loan payment. Additional parameters about the loan are provided in cells P$11$ through P$13-$ you must use appropriate references to these in your formula. Copy the formula to the cell range L$5$ through L$12.$ Hint: You will need to $6$iuse the PMT function in your formula to answer this step correctly.

In cell F$14,$ write a formula that uses the COUNTIF function to determine the total number of WooHoo rated sales reps.

$4.$To answer this correctly you must test for the value WooHoo.

In cell F$15,$ write a formula that uses the AVERAGEIF function to determine the average total sales of all of the WooHoo rated sales $4$'reps. To answer this correctly you must test for the value WooHoo.

Select the cell ranges A$3$ through A$12$ and $13$ through $112$ and create a $3-$D Clustered Column chart. If there are two options $4$ishown for this chart, select the one that has the horizontal axis displaying the sales rep names.

$3,14.$ Move the chart to its own chart sheet and name the chart sheet Sales Rep Performance Chart.

Sales Rep Performance Chart worksheet

Apply the Style $3$ chart style to the chart.

Set the title of the chart to Sales Rep Performance.

Grading

Sales Rep Performance

Purchases