3 In cell H9, insert the YEARFRAC function to calculate the years between the hire date and the last day of the year contained in cell G2 Use a mixed reference to cell G2 Copy the function from cell H9 to the range H10 H33 4 You want to determine what day of the week each employee was hired In cell I9, insert the WEEKDAY function to display the day of the week the first employee was hired Use 2 as the return type Copy the function from cell I9 to the range I10 I33 5 The value returned in cell I9 is a whole number You want to display the weekday equivalent In cell J9, insert a VLOOKUP function to look up the value stored in cell I9, compare it to the array in the range H2 I6, and return the day of the week Use mixed references to the table array Copy the function from cell J9 to the range J10 J33 6 Column D contains the city each employee works in You want to display the state In cell F9, insert the SWITCH function to switch the city stored in cell D9 with the respective state contained in the range C2 C4 Switch Des Moines for Iowa, St Paul for Minnesota, and Milwaukee for Wisconsin Use mixed references to cells C2, C3, and C4 Copy the function from cell F9 to the range F10 F33 7 Your next task is to calculate the bonus for the first employee If the employee was hired before 1 1 2010, the employee's salary is multiplied by 3 If the employee was hired before 1 1 2015, the employee's salary is multiplied by 2 If the employee was hired before 1 1 2020, the employee's salary is multiplied by 1 In cell K9, insert the IFS function to create the three logical tests to calculate the appropriate bonus Use mixed references to cells within the range K2 L4 Copy the function from cell K9 to the range K10 K33 8 Top management decided to ensure all representatives' salaries are at least $62,000 (cell G2) In cell L9, nest an AND function within an IF function If the job title is Representative and the salary is less than the minimum representative salary, calculate the difference between the minimum representative salary and the actual salary If not, return zero Use a mixed reference to cell G3 Copy the function from cell L9 to the range L10 L33 9 The 2 Summary worksheet contains data to insert conditional math and statistical functions to provide summary data First, you want to count the number of employees in each state Click the 2 Summary worksheet In cell J3, insert the COUNTIF function to count the number of employees in Iowa, using the state abbreviation column and the state abbreviation in cell I3 Use mixed references for the range and cell to keep the row numbers the same Copy the function from cell J3 to the range J4 J5 10 In cell K3, insert the SUMIF function to total the salaries for employees who work in Iowa, using the state abbreviation column and the state abbreviation in cell I3 Use mixed references for the ranges and cell to keep the row numbers the same Copy the function from cell K3 to the range K4 K5 11 In cell J8, insert the AVERAGEIF function to calculate the average salary for executives Use mixed references to the range and for the cell containing Executives Copy the function to cell J9 12 In cell J12, insert the COUNTIFS function to count the number of representatives who were hired before 1 1 2015 Use ranges for the criteria ranges and the reference to cell H9 that contains Representatives Enter the date condition directly in the criteria box 13 In cell J13, insert the SUMIFS function to calculate the total salaries for representatives hired before 1 1 2015 Use ranges for the criteria ranges and the reference to cell H9 that contains Representatives Enter the date condition directly in the criteria box Use mixed references to the ranges and cell reference so that you can copy the function in the next step 14 Copy the function from cell J13 to the range J14 J16 Edit the function in cell J14 to calculate averages 15 Edit the function in cell J15 to calculate the lowest salary 16 Edit the function in cell J16 to calculate the highest salary 17 Select the ranges I2 I5 and K2 K5 and insert a filled map Change the chart title to Payroll by State Cut the chart and paste it in cell M1 18 In cell F2, insert the PV function to calculate the loan Use cell references in the Input Area and Basic Output Area 19 In cell C7, enter a reference to the Payment that is stored in cell D2 Use mixed reference so that the row number does not change Copy the formula from cell C7 to the range C8 C42 20 In cell D7, insert the IPMT function to calculate the interest for the first payment Use mixed references for cells in the Basic Output Area and make sure the result is a positive value Copy the function from cell D7 to the range D8 D42 21 In cell E7, insert the PPMT function to calculate the principal portion of the first payment Use mixed references for cells in the Basic Output Area and make sure the result is a positive value Copy the function from cell E7 to the range E8 E42 22 In cell F7, enter a formula to calculate the ending balance after subtracting the Principal Repayment from the Beginning Balance Copy the formula from cell F7 to the range F8 F42 In cell B8, enter a formula that refers to the ending balance for the previous month in cell F7 Copy the relative formula to the range B9 B42 23 In cell H7, insert the CUMIPMT function Use mixed references for cells in the Basic Output Area and the Start period argument Make sure the result is a positive value Copy the function from cell H7 to the range H8 H42 24 In cell I7, insert the CUMPRINC function Use mixed references for cells in the Basic Output Area and the Start period argument Make sure the result is a positive value Copy the function from cell I7 to the range I8 I42 D States lowa Minnesota Wisconsin Comparison Data Last Day of Year Minimum Representative Salary H Day Code 12 31 21 1 62,000 2 3 3 4 5 s Weekday Monday Tuesday Wednesday Thursday Friday Bonus Decision Hired before Hired before Hired before K Date Percent 1 1 10 3 3 1 1 15 2 1 1 20 1 Input Area Hire Date Code Day of Week Hired Region Years Employed Bonus Raise Hire Date City 8 30 19 St Paul 9 18 14 Milwaukee 11 7 18 St Paul 10 18 07 Des Moines 1 8 20 Milwaukee 6 16 14 Milwaukee 9 3 15 St Paul 11 6 15 Milwaukee 12 14 09 St Paul 4 3 20 St Paul 8 13 20 Des Moines 1 17 11 Des Moines 6 5 14 Milwaukee 7 15 14 Milwaukee 4 15 09 St Paul 3 3 14 St Paul 2 18 08 Milwaukee 21 8 16 Milwaukee 6 15 13 St Paul 10 11 16 St Paul 7 14 20 Des Moines 4 17 18 St Paul 1 31 14 Des Moines 4 17 12 Milwaukee 3 9 20 St Paul C Salary $ $ 125,350 IS 65,000 $ 54,000 $ 105,750 $ 55,125 $ 70,500 $ 65,400 $ 64,750 $ 74,350 $ 55,120 $ 61,000 IS $ 65,750 $ 70,600 $ 115,750 $ 75,100 $ 73,125 $ 75,180 IS 63,000 $ 118,000 $ 53,800 $ 61,700 $ 55,750 $ 63,420 $ 72 900 IS $ 53,750 D Year Hired 2019 2014 2018 2007 2020 2014 2015 2015 2009 2020 2020 2011 2014 2014 2009 2014 2008 2016 2013 2016 2020 2018 2014 2012 2020 E F G H K Employees Payroll Name Title 9 Aalders Executive 10 Babin Representative 11 Bernhardsson Representative 12 Chavdarov Executive 13 Dar Representative 14 Duff Representative 15 Esteves Representative 16 Fabron Representative 17 Griffith Representative 18 Guan Representative 19 Halloran Representative 20 lordanou Representative 21 Janowski Representative 22 Kimura Executive 23 Lim Representative 24 Mantovani Representative 25 Nicolas Representative 26 Owens Representative 27 Pachis Executive 28 Quincy Representative 29 Reece Representative 30 Saito Representative 31 Tennison Representative 32 Ungur Representative 33 Vernon Representative B 1 Name Title 2 Aalders Executive 3 Babin Representative Bernhardssc Representative Chavdarov Executive 6 Diaz Representative 7 Duff Representative 8 Esteves Representative 9 Fabron Representative 10 Griffith Representative 11 Guan Representative 12 Halloran Representative 13 Iordanou Representative 14 Janowski Representative 15 Kimura Executive 16 Lim Representative 17 Mantovani Representative 18 Nicolas Representative 19 Owens Representative 20 Pachis Executive 21 Quincy Representative 22 Reece Representative 23 Saito Representative 24 Tennison Representative 25 Ungur Representative 26 Vemon Representative 27 Summary by Location City State Des Moines St Paul MN Milwaukee WI State MN WI MN IA WI WI MN WI MN MN IA Summary Average Salaries Executive Representative IA Hire Date City 8 30 19 St Paul 9 18 14 Milwaukee 11 7 18 St Paul 10 18 07 Des Moines 1 8 20 Milwaukee 6 16 14 Milwaukee 9 3 15 St Paul 11 6 15 Milwaukee 12 14 09 St Paul 4 3 20 St Paul 8 13 20 Des Moines 1 17 11 Des Moines 6 5 14 Milwaukee 7 15 14 Milwaukee 4 15 09 St Paul 3 3 14 St Paul 2 18 08 Milwaukee 11 8 16 Milwaukee 6 15 13 St Paul 10 11 16 St Paul 7 14 20 Des Moines 4 17 18 St Paul 1 31 14 Des Moines 4 17 12 Milwaukee 3 9 20 St Paul Salary $ 125,350 $ s 65,000 $ 54,000 $ 105,750 $ 55,125 $ 70,500 $ 65,400 $ 64 750 S 74,350 $ 55,120 $ 61,000 $ 65,750 $ 70,600 $ 115,750 $ 75,100 S $ 73,125 $ 75,180 $ 63,000 $ 118,000 S 53,800 $ 61,700 $ 55,750 $ 63,420 $ 72,900 $ 53,750 Representative Summary Statistics Hired Before 1 1 2015 Total $ Hired

The Answer is in the image, click to view ...

Question: 3 In cell H9, insert the YEARFRAC function to calculate the years between the hire date and the last day of the year contained in

3	In cell H9, insert the YEARFRAC function to calculate the years between the hire date and the last day of the year contained in cell G2. Use a mixed reference to cell G2. Copy the function from cell H9 to the range H10:H33.
4	You want to determine what day of the week each employee was hired. In cell I9, insert the WEEKDAY function to display the day of the week the first employee was hired. Use 2 as the return_type. Copy the function from cell I9 to the range I10:I33.
5	The value returned in cell I9 is a whole number. You want to display the weekday equivalent. In cell J9, insert a VLOOKUP function to look up the value stored in cell I9, compare it to the array in the range H2:I6, and return the day of the week. Use mixed references to the table array. Copy the function from cell J9 to the range J10:J33.
6	Column D contains the city each employee works in. You want to display the state. In cell F9, insert the SWITCH function to switch the city stored in cell D9 with the respective state contained in the range C2:C4. Switch Des Moines for Iowa, St. Paul for Minnesota, and Milwaukee for Wisconsin. Use mixed references to cells C2, C3, and C4. Copy the function from cell F9 to the range F10:F33.
7	Your next task is to calculate the bonus for the first employee. If the employee was hired before 1/1/2010, the employee's salary is multiplied by 3%. If the employee was hired before 1/1/2015, the employee's salary is multiplied by 2%. If the employee was hired before 1/1/2020, the employee's salary is multiplied by 1%. In cell K9, insert the IFS function to create the three logical tests to calculate the appropriate bonus. Use mixed references to cells within the range K2:L4. Copy the function from cell K9 to the range K10:K33.
8	Top management decided to ensure all representatives' salaries are at least $62,000 (cell G2). In cell L9, nest an AND function within an IF function. If the job title is Representative and the salary is less than the minimum representative salary, calculate the difference between the minimum representative salary and the actual salary. If not, return zero. Use a mixed reference to cell G3. Copy the function from cell L9 to the range L10:L33.
9	The 2-Summary worksheet contains data to insert conditional math and statistical functions to provide summary data. First, you want to count the number of employees in each state. Click the 2-Summary worksheet. In cell J3, insert the COUNTIF function to count the number of employees in Iowa, using the state abbreviation column and the state abbreviation in cell I3. Use mixed references for the range and cell to keep the row numbers the same. Copy the function from cell J3 to the range J4:J5.
10	In cell K3, insert the SUMIF function to total the salaries for employees who work in Iowa, using the state abbreviation column and the state abbreviation in cell I3. Use mixed references for the ranges and cell to keep the row numbers the same. Copy the function from cell K3 to the range K4:K5.
11	In cell J8, insert the AVERAGEIF function to calculate the average salary for executives. Use mixed references to the range and for the cell containing Executives. Copy the function to cell J9.
12	In cell J12, insert the COUNTIFS function to count the number of representatives who were hired before 1/1/2015. Use ranges for the criteria ranges and the reference to cell H9 that contains Representatives. Enter the date condition directly in the criteria box.
13	In cell J13, insert the SUMIFS function to calculate the total salaries for representatives hired before 1/1/2015. Use ranges for the criteria ranges and the reference to cell H9 that contains Representatives. Enter the date condition directly in the criteria box. Use mixed references to the ranges and cell reference so that you can copy the function in the next step.
14	Copy the function from cell J13 to the range J14:J16. Edit the function in cell J14 to calculate averages.
15	Edit the function in cell J15 to calculate the lowest salary.
16	Edit the function in cell J16 to calculate the highest salary.
17	Select the ranges I2:I5 and K2:K5 and insert a filled map. Change the chart title to Payroll by State. Cut the chart and paste it in cell M1.
18	In cell F2, insert the PV function to calculate the loan. Use cell references in the Input Area and Basic Output Area.
19	In cell C7, enter a reference to the Payment that is stored in cell D2. Use mixed reference so that the row number does not change. Copy the formula from cell C7 to the range C8:C42.
20	In cell D7, insert the IPMT function to calculate the interest for the first payment. Use mixed references for cells in the Basic Output Area and make sure the result is a positive value. Copy the function from cell D7 to the range D8:D42.
21	In cell E7, insert the PPMT function to calculate the principal portion of the first payment. Use mixed references for cells in the Basic Output Area and make sure the result is a positive value. Copy the function from cell E7 to the range E8:E42.
22	In cell F7, enter a formula to calculate the ending balance after subtracting the Principal Repayment from the Beginning Balance. Copy the formula from cell F7 to the range F8:F42. In cell B8, enter a formula that refers to the ending balance for the previous month in cell F7. Copy the relative formula to the range B9:B42.
23	In cell H7, insert the CUMIPMT function. Use mixed references for cells in the Basic Output Area and the Start_period argument. Make sure the result is a positive value. Copy the function from cell H7 to the range H8:H42.
24	In cell I7, insert the CUMPRINC function. Use mixed references for cells in the Basic Output Area and the Start_period argument. Make sure the result is a positive value. Copy the function from cell I7 to the range I8:I42.

3 In cell H9, insert the YEARFRAC function to

D States lowa Minnesota Wisconsin Comparison Data Last Day of Year Minimum Representative Salary H Day Code 12/31/21 1 62,000 2 3 3 4 5 s Weekday Monday Tuesday Wednesday Thursday Friday Bonus Decision Hired before Hired before Hired before K Date Percent 1/1/10 3% 3% 1/1/15 2% 1/1/20 1% Input Area Hire Date Code Day of Week Hired Region Years Employed Bonus Raise Hire Date City 8/30/19 St. Paul 9/18/14 Milwaukee 11/7/18 St. Paul 10/18/07 Des Moines 1/8/20 Milwaukee 6/16/14 Milwaukee 9/3/15 St. Paul 11/6/15 Milwaukee 12/14/09 St. Paul 4/3/20 St. Paul 8/13/20 Des Moines 1/17/11 Des Moines 6/5/14 Milwaukee 7/15/14 Milwaukee 4/15/09 St. Paul 3/3/14 St. Paul 2/18/08 Milwaukee 21/8/16 Milwaukee 6/15/13 St. Paul 10/11/16 St. Paul 7/14/20 Des Moines 4/17/18 St. Paul 1/31/14 Des Moines 4/17/12 Milwaukee 3/9/20 St. Paul C Salary $ $ 125,350 IS 65,000 $ 54,000 $ 105,750 $ 55,125 $ 70,500 $ 65,400 $ 64,750 $ 74,350 $ 55,120 $ 61,000 IS $ 65,750 $ 70,600 $ 115,750 $ 75,100 $ 73,125 $ 75,180 IS 63,000 $ 118,000 $ 53,800 $ 61,700 $ 55,750 $ 63,420 $ 72.900 IS $ 53,750 D Year Hired 2019 2014 2018 2007 2020 2014 2015 2015 2009 2020 2020 2011 2014 2014 2009 2014 2008 2016 2013 2016 2020 2018 2014 2012 2020 E F G H K # Employees Payroll Name Title 9 Aalders Executive 10 Babin Representative 11 Bernhardsson Representative 12 Chavdarov Executive 13 Dar Representative 14 Duff Representative 15 Esteves Representative 16 Fabron Representative 17 Griffith Representative 18 Guan Representative 19 Halloran Representative 20 lordanou Representative 21 Janowski Representative 22 Kimura Executive 23 Lim Representative 24 Mantovani Representative 25 Nicolas Representative 26 Owens Representative 27 Pachis Executive 28 Quincy Representative 29 Reece Representative 30 Saito Representative 31 Tennison Representative 32 Ungur Representative 33 Vernon Representative B 1 Name Title 2 Aalders Executive 3 Babin Representative Bernhardssc Representative Chavdarov Executive 6 Diaz Representative 7 Duff Representative 8 Esteves Representative 9 Fabron Representative 10 Griffith Representative 11 Guan Representative 12 Halloran Representative 13 Iordanou Representative 14 Janowski Representative 15 Kimura Executive 16 Lim Representative 17 Mantovani Representative 18 Nicolas Representative 19 Owens Representative 20 Pachis Executive 21 Quincy Representative 22 Reece Representative 23 Saito Representative 24 Tennison Representative 25 Ungur Representative 26 Vemon Representative 27 Summary by Location City State Des Moines St. Paul MN Milwaukee WI State MN WI MN IA WI WI MN WI MN MN IA Summary Average Salaries Executive Representative IA Hire Date City 8/30/19 St. Paul 9/18/14 Milwaukee 11/7/18 St. Paul 10/18/07 Des Moines 1/8/20 Milwaukee 6/16/14 Milwaukee 9/3/15 St. Paul 11/6/15 Milwaukee 12/14/09 St. Paul 4/3/20 St. Paul 8/13/20 Des Moines 1/17/11 Des Moines 6/5/14 Milwaukee 7/15/14 Milwaukee 4/15/09 St. Paul 3/3/14 St. Paul 2/18/08 Milwaukee 11/8/16 Milwaukee 6/15/13 St. Paul 10/11/16 St. Paul 7/14/20 Des Moines 4/17/18 St. Paul 1/31/14 Des Moines 4/17/12 Milwaukee 3/9/20 St. Paul Salary $ 125,350 $ s 65,000 $ 54,000 $ 105,750 $ 55,125 $ 70,500 $ 65,400 $ 64.750 S 74,350 $ 55,120 $ 61,000 $ 65,750 $ 70,600 $ 115,750 $ 75,100 S $ 73,125 $ 75,180 $ 63,000 $ 118,000 S 53,800 $ 61,700 $ 55,750 $ 63,420 $ 72,900 $ 53,750 Representative Summary Statistics Hired Before 1/1/2015 Total $ Hired

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!

Analyze the outdoor lifestyle data given in Internet and Computer Exercises 2 of Chapter 15. Do the three groups based on location of residence differ on the importance attached to enjoying nature,...

Next, you want to determine how many years each employee has worked for the company. In cell H 9 , insert the YEARFRAC function to calculate the years between the hire date and the last day of the...

Project Description: You work for a dothing distributor that has locations in lowa, Minnesota, and Wisconsin. You will use date and logical functions to complete the main ernployee data set, use...

Exp 2 2 _ Excel _ Ch 0 7 _ HOE _ Salaries In cell G 7 , insert the YEARFRAC function using the Hire Date and Last Day of the Year. Use relative and mixed references correctly. Use the fill handle to...

Auto 200 A Malah aplic.CHT.HOLA, Eleme le Home Page Layout To View Costa -20-AA wepo Pate 3 10- aA. DE Merge - $ %) Fom - Candi ng Al Input Area M 0 D Icomparata ) tes De Per 12/31/10 Tar Cote 1 2...

Grader - Instructions Excel 2019 Project Exp19_Excel_Ch07_HOEAssessment Employees Project Description: You work for a clothing distributor that has locations in lowa, Minnesota, and Wisconsin. You...

excel problems can you inclue the equations so i can study from them. Steps to Perform: Stop Instructions Points Possible 1 O 2 3 Start Excel. Download and open the file named...

5 6 The top-left section of the spreadsheet is designed for summary statistics for one condition. You will calculate average selling prices and the number of houses sold in each city (the condition)....

12-23 Thank you 12 On the Map worksheet, insert a map for the states and revenues. Cut and paste the map in cell C1 5 13 Format the data series to show only regions with data and show all map labels....

Step by step year. Use A9 for the Start_period argument and B6 for the End period argument, Use 0 as the Type argument. Make sure the result is a positive value. 10 Now you want to calculate the...

Project Description: The Painted Paradise Resort and Spa has been investing in advertising using different media. When guests check in, the employee asks them how they heard about Painted Paradise...

Marketing depend on nostalgia marketing more over to sell products. Why?

A rigid bar ABif of a length B= 66 in. is. hinged to a support at A and supported by two vertical wires attached at points C and D (see figure). Both wires have the same cross-sectional area (A =...

The diagram shows the sizes of the two equal angles in an isosceles triangle. a Write an equation to represent the problem. b Solve your equation to find the value of x. c Work out the size of each...

Case Greenbuilders Pte Lid ("GPL") is a local construction company which specialises in providing bespoke Design and Build construction services for private residential homes that are designed to be...