Test 2 Excel Practice(1)

5c406777892e6b5661dd7859269e673b8fffcd49.xlsx INSTRUCTIONS: 1 2 3 4 5 In Column P, use a function to calculate the average sales for each employee (January to June). 6 6a 6b 6c Convert the format of Column Q to Percent with no decimal places. 7 8 In Column S, use a function to convert the EmployeeID to uppercase 9 In Column T, use a function to get the length of each employee's ID 10 In I27 through N27, calculate the total monthly sales for January through June 11 12 In J37 use a function to add the total sales for employees located in Atlanta 13 14 In J41, extract the first four characters of the Employee ID in R7 15 In J45, extract the last 4 characters of the Employee ID in R9 In Column D, use a function to extract the month from the employee's hire date. You may have to convert the format of the data in Column D to General In Column E, use a function to extract the year from the employee's hire date. You may have to convert the format of the data in Column D to General Using the information in V8:W15, in Column H, use a function to lookup the region based on the city the employee is located in. Use an approximate match Use a function (and cell references) to add the totals for January to June for each salesperson in Column O. In Column Q, use a function with the information in V3:W5 to determine the employee's bonus percentage. If the average sales for the employee is less than $30,000 --> the employee receives a 2% bonus If the average sales for the employee is more than $30,000 --> the employee receives a 4% bonus In Column R, use a function to create the employee ID. The function should concatenate the employee's last name, hire month, and city. For example: Oliver9Atlanta In J34 use a function to count the number of people located in Miami with average sales greater than $40,000. Create a column chart showing the monthly sales in I27:N27. The chart should have axis labels and axis titles. Move the chart to a new sheet named 'Sales by Month'

Last Name First Name Hire Date Hire Month Hire Year City State Region January Rogers Nelson Prince 9/30/2000 Atlanta GA $31,520 Idol Billy 8/10/2001 Miami FL $41,253 Jackson Janet 5/21/2012 Los Angeles CA $28,016 Jackson Michael 10/30/2013 Chicago IL $33,465 Runn DMC 11/10/2002 Miami FL $30,468 Carter Shawn 3/18/2015 Chicago IL $34,716 Khan Chaka 4/1/2002 Buffalo NY $48,051 Lamar Kendrick 8/13/2020 Chicago IL $16,400 Cole Jermaine 5/24/2002 Buffalo NY $28,468 Rakim Eric 4/25/2010 Atlanta GA $21,520 Michael George 12/12/1997 Buffalo NY $31,253 Yankovic Al 10/23/2014 Los Angeles CA $37,016 Graham Aubrey 4/25/2016 Buffalo NY $36,465 Turner Tina 2/1/2011 Atlanta GA $37,915 Carter Dwayne 4/3/2002 Buffalo NY $23,789 Nicks Stevie 10/7/2011 Los Angeles CA $48,158 Houston Whitney 10/20/2005 Miami FL $16,400 Benatar Pat 6/22/2005 Chicago IL $19,815 West Kanye 5/20/2007 Chicago IL $21,779 Benjamin Andre 1/24/2017 Atlanta GA $23,312 TOTALS

How many people located in Miami have average sales greater than $40,000? ANSWER: What is the sum of total sales for employees located in Atlanta? ANSWER: Extract the first four characters of the Employee ID in R7 ANSWER: Extract the last 4 characters of the Employee ID in R9 ANSWER:

IF 4% 2% ID Length Atlanta South Miami South Los Angeles West Las Vegas West Chicago Midwest San Francisco West Buffalo Northeast Philadelphia East Average Sales > 30,000 Average Sales < 30,000 Uppercase Password