here is the excel file
Points Possible Step Instructions Download and open the file named exploring_e08_grader_a1.xlsx, and then save the file as exploring_e08_grader_a1_LastFirst, replacing LastFist with your name. 1 0 Enter a conditional function in cell 15 to calculate average satisfaction for support staff (H5) Format the results with Number format and two decimal positions. 2 6 3 Use the fill handle in cell 15 to copy the function down through the range 16:19. Be sure to use the appropriate mixed or absolute referencing before copying the functions 3 Enter a function in cell J5 to calculate the average salary of all support staff (H5) in the survey 4 6 5 Use the fill handle in cell J5 to copy the function down through the range J6:J9. Be sure to use the appropriate mixed or absolute referencing before copying the functions 3 6 Enter a function in cell 112 to calculate the number of Directors in the survey that have a job satisfaction level of 4 or higher 6 7 Enter a function in cell l13 to calculate the average salary of Directors in the survey that have a job satisfaction level of 4 or higher 6 8 Adapt the process used in the previous two steps to calculate the total number and average salary of managers that have a job satisfaction of 4 or higher in cells l16 and 117. 6 Enter a function in cell F4 that calculates the rank of the salary in cell D4 against the range of 6 salaries in the data set. Use the fill handle to copy the function down column F. Be sure to include the appropriate absolute or mixed cell references before copying the functions. 5 11 Enter a function in cell 120 to calculate the minimum Quartile value in the list of salaries. 6 12 Use the fill handle to complete the remaining quartile values in cell range 121:124. Be sure to include the appropriate absolute or mixed cell references before copying the functions. 5 Enter a function in cell H27 to calculate the correlation of column D and E 13 12 14 Format the result as Number Format with two decimal positions. 5 15 If necessary, display the Analysis JoolPak Run a Data Analysis with descriptive statistics. Complete the input criteria using the salary data in the range D4:D53. Set the Output functions to display on a new worksheet. Set the output as Summary statistics 12 Name the newly created worksheet Descriptive Statistics 3 17 On the Employee Satisfaction worksheet, run a Data Analysis histogram. Use the salaries in the range D4:D53 as the input range. Use the quartiles in the range 120:124 as the bin range. Output the data in cell H29. Include a chart with the output 10 CO 10 16 A E F G 1 Employee Statistics Salary 2 ID Number Position Job Satisfaction Salary Rank Summary Information 3 Position Support Staff Sales Rep Average Satisfaction 1047 Sales Rep Average Salary 45,855.00 46,063.00 4 3 1085 Sales Rep 5 4 1102 Sale Rep 1106 Sales Rep 45,700.00 2 47,572.00 7 3 Accountant 1165 Support Staff 1473 Sales Rep 30,176.00 8 4 Manager 45,993.00 9 4 Director 1486 Support Staff 10 32,666.00 1 1503 Support Staff 1519 Sale Rep Directors with > 4 Satisfaction 11 32,757.00 3 45,674.00 Count 12 Average Salary 13 1529 Director 88,197.00 5 89,691.00 14 1601 Director 4 Managers with > 4 Satisfaction 1603 Accountant 15 69,389.00 2 1675 Sale Rep 16 49,617.00 Count 1828 Support Staff 35,230.00 Average Salary 17 3 83,240.00 18 2003 Director Quartile Salary 2206 Sale Rep 19 42,383.00 92,700.00 20 2250 Director 0 2291 Support Staff 2292 Sales Rep 21 35,242.00 1 22 44,854.00 2 2 77,846.00 23 2416 Director 3 2528 Manager 2624 Manager 24 70,125.00 3 25 73,564.00 5 2742 Accountant Correlation 26 62,263.00 4 3004 Support Staff 30,982.00 27 2 A D E F G 3004 Support Staff 27 30,982.00 2 84,937.00 28 3083 Director 4 3161 Support Staff 3217 Sales Rep 32,709.00 29 4 30 40,449.00 3314 Sales Rep 45,983.00 31 5 32 3338 Support Staff 3402 Support Staff 3473 Support Staff 3638 Sales Rep 3652 Sales Rep 36,942.00 3 33 33,852.00 5 33,501.00 34 1 35 48,005.00 3 48,706.00 36 5 3782 Sale Rep 37 45,927.00 2 3808 Support Staff 3818 Sale Rep 38 31,632.00 5 39 49,575.00 3 3874 Support Staff 3877 Support Staff 40 34,154.00 4 34,775.00 41 3 42 3948 Support Staff 37,404.00 4 43 3969 Accountant 65,492.00 44 4239 Support Staff 32,107.00 4 4243 Support Staff 35,316.00 45 2 4269 Accountant 68,365.00 46 2 4398 Sales Rep 47 41,130.00 4 4541 Manager 48 72,862.00 4584 Support Staff 49 30,701.00 5 4652 Accountant 66,009.00 50 2 4771 Manager 51 74,767.00 4 4911 Director 52 81,082.00 4 53 4954 Director 97,159.00 5 Points Possible Step Instructions Download and open the file named exploring_e08_grader_a1.xlsx, and then save the file as exploring_e08_grader_a1_LastFirst, replacing LastFist with your name. 1 0 Enter a conditional function in cell 15 to calculate average satisfaction for support staff (H5) Format the results with Number format and two decimal positions. 2 6 3 Use the fill handle in cell 15 to copy the function down through the range 16:19. Be sure to use the appropriate mixed or absolute referencing before copying the functions 3 Enter a function in cell J5 to calculate the average salary of all support staff (H5) in the survey 4 6 5 Use the fill handle in cell J5 to copy the function down through the range J6:J9. Be sure to use the appropriate mixed or absolute referencing before copying the functions 3 6 Enter a function in cell 112 to calculate the number of Directors in the survey that have a job satisfaction level of 4 or higher 6 7 Enter a function in cell l13 to calculate the average salary of Directors in the survey that have a job satisfaction level of 4 or higher 6 8 Adapt the process used in the previous two steps to calculate the total number and average salary of managers that have a job satisfaction of 4 or higher in cells l16 and 117. 6 Enter a function in cell F4 that calculates the rank of the salary in cell D4 against the range of 6 salaries in the data set. Use the fill handle to copy the function down column F. Be sure to include the appropriate absolute or mixed cell references before copying the functions. 5 11 Enter a function in cell 120 to calculate the minimum Quartile value in the list of salaries. 6 12 Use the fill handle to complete the remaining quartile values in cell range 121:124. Be sure to include the appropriate absolute or mixed cell references before copying the functions. 5 Enter a function in cell H27 to calculate the correlation of column D and E 13 12 14 Format the result as Number Format with two decimal positions. 5 15 If necessary, display the Analysis JoolPak Run a Data Analysis with descriptive statistics. Complete the input criteria using the salary data in the range D4:D53. Set the Output functions to display on a new worksheet. Set the output as Summary statistics 12 Name the newly created worksheet Descriptive Statistics 3 17 On the Employee Satisfaction worksheet, run a Data Analysis histogram. Use the salaries in the range D4:D53 as the input range. Use the quartiles in the range 120:124 as the bin range. Output the data in cell H29. Include a chart with the output 10 CO 10 16 A E F G 1 Employee Statistics Salary 2 ID Number Position Job Satisfaction Salary Rank Summary Information 3 Position Support Staff Sales Rep Average Satisfaction 1047 Sales Rep Average Salary 45,855.00 46,063.00 4 3 1085 Sales Rep 5 4 1102 Sale Rep 1106 Sales Rep 45,700.00 2 47,572.00 7 3 Accountant 1165 Support Staff 1473 Sales Rep 30,176.00 8 4 Manager 45,993.00 9 4 Director 1486 Support Staff 10 32,666.00 1 1503 Support Staff 1519 Sale Rep Directors with > 4 Satisfaction 11 32,757.00 3 45,674.00 Count 12 Average Salary 13 1529 Director 88,197.00 5 89,691.00 14 1601 Director 4 Managers with > 4 Satisfaction 1603 Accountant 15 69,389.00 2 1675 Sale Rep 16 49,617.00 Count 1828 Support Staff 35,230.00 Average Salary 17 3 83,240.00 18 2003 Director Quartile Salary 2206 Sale Rep 19 42,383.00 92,700.00 20 2250 Director 0 2291 Support Staff 2292 Sales Rep 21 35,242.00 1 22 44,854.00 2 2 77,846.00 23 2416 Director 3 2528 Manager 2624 Manager 24 70,125.00 3 25 73,564.00 5 2742 Accountant Correlation 26 62,263.00 4 3004 Support Staff 30,982.00 27 2 A D E F G 3004 Support Staff 27 30,982.00 2 84,937.00 28 3083 Director 4 3161 Support Staff 3217 Sales Rep 32,709.00 29 4 30 40,449.00 3314 Sales Rep 45,983.00 31 5 32 3338 Support Staff 3402 Support Staff 3473 Support Staff 3638 Sales Rep 3652 Sales Rep 36,942.00 3 33 33,852.00 5 33,501.00 34 1 35 48,005.00 3 48,706.00 36 5 3782 Sale Rep 37 45,927.00 2 3808 Support Staff 3818 Sale Rep 38 31,632.00 5 39 49,575.00 3 3874 Support Staff 3877 Support Staff 40 34,154.00 4 34,775.00 41 3 42 3948 Support Staff 37,404.00 4 43 3969 Accountant 65,492.00 44 4239 Support Staff 32,107.00 4 4243 Support Staff 35,316.00 45 2 4269 Accountant 68,365.00 46 2 4398 Sales Rep 47 41,130.00 4 4541 Manager 48 72,862.00 4584 Support Staff 49 30,701.00 5 4652 Accountant 66,009.00 50 2 4771 Manager 51 74,767.00 4 4911 Director 52 81,082.00 4 53 4954 Director 97,159.00 5
