Not sure if I can attach the actual Excel file or not... and just realized i took a picture of row 27 twice...

Thanks!

Step Instructions Points Possible Download and open the file named exploring e08 grader a1.xlsx, and then save the file as exploring_e08_grader_a1_LastFirst, replacing LastFirst with your name. 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. 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. Enter a function in cell J5 to calculate the average salary of all support staff (H5) in the survey. Use the fill handle in cell J5 to copy the function down through the range J6:19. Be sure to use the appropriate mixed or absolute referencing before copying the functions. 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. Enter a function in cell 113 to calculate the average salary of Directors in the survey that have a job satisfaction level of 4 or higher. 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 116 and 117. Enter a function in cell F4 that calculates the rank of the salary in cell D4 against the range of 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. Enter a function in cell 120 to calculate the minimum Quartile value in the list of salaries. 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. Enter a function in cell H27 to calculate the correlation of column D and E. 14 Format the result as Number Format with two decimal positions. If necessary, display the Analysis ToolPak 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. 16 Name the newly created worksheet Descriptive Statistics. 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. B C D E F G H I Average Salary Summary Information Position Average Satisfaction Support Staff Sales Rep Accountant Manager Director 10 11 12 Directors with > 4 Satisfaction Count Average Salary 13 ID Number Position 1047 Sales Rep 1085 Sales Rep 1102 Sale Rep 1106 Sales Rep 1165 Support Staff 1473 Sales Rep 1486 Support Staff 1503 Support Staff 1519 Sale Rep 1529 Director 1601 Director 1603 Accountant 1675 Sale Rep 1828 Support Staff 2003 Director 2206 Sale Rep 2250 Director 2291 Support Staff 2292 Sales Rep 2416 Director 2528 Manager 2624 Manager 2742 Accountant 3004 Support Staff Employee Statistics Salary Job Satisfaction Salary Rank 45,855.00 46,063.00 45,700.00 47,572.00 30,176.00 45,993.00 32,666.00 32,757.00 45,674.00 88,197.00 89,691.00 69,389.00 49,617.00 35,230.00 83,240.00 42,383.00 92,700.00 35,242.00 44,854.00 77,846.00 70,125.00 73,564.00 62,263.00 30,982.00 mnm + + minin nmmnm mmm inn 16 Managers with > 4 Satisfaction Count Average Salary 18 19 Quartile Salary 24 26 Correlation 27 D 36 AB 3004 Support Staff 3083 Director 3161 Support Staff 3217 Sales Rep 3314 Sales Rep 3338 Support Staff 3402 Support Staff 34 3473 Support Staff 35 3638 Sales Rep 3652 Sales Rep 37 3782 Sale Rep 38 3808 Support Staff 39 3818 Sale Rep 3874 Support Staff 3877 Support Staff 42 3948 Support Staff 3969 Accountant 4239 Support Staff 4243 Support Staff 46 4269 Accountant 4398 Sales Rep 4541 Manager 4584 Support Staff 4652 Accountant 4771 Manager 4911 Director 4954 Director 30,982.00 84,937.00 32,709.00 40,449.00 45,983.00 36,942.00 33,852.00 33,501.00 48,005.00 48,706.00 45,927.00 31,632.00 49,575.00 34,154.00 34,775.00 37,404.00 65,492.00 32,107.00 35,316.00 68,365.00 41,130.00 72,862.00 30,701.00 66,009.00 74,767.00 81,082.00 97,159.00 Step Instructions Points Possible Download and open the file named exploring e08 grader a1.xlsx, and then save the file as exploring_e08_grader_a1_LastFirst, replacing LastFirst with your name. 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. 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. Enter a function in cell J5 to calculate the average salary of all support staff (H5) in the survey. Use the fill handle in cell J5 to copy the function down through the range J6:19. Be sure to use the appropriate mixed or absolute referencing before copying the functions. 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. Enter a function in cell 113 to calculate the average salary of Directors in the survey that have a job satisfaction level of 4 or higher. 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 116 and 117. Enter a function in cell F4 that calculates the rank of the salary in cell D4 against the range of 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. Enter a function in cell 120 to calculate the minimum Quartile value in the list of salaries. 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. Enter a function in cell H27 to calculate the correlation of column D and E. 14 Format the result as Number Format with two decimal positions. If necessary, display the Analysis ToolPak 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. 16 Name the newly created worksheet Descriptive Statistics. 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. B C D E F G H I Average Salary Summary Information Position Average Satisfaction Support Staff Sales Rep Accountant Manager Director 10 11 12 Directors with > 4 Satisfaction Count Average Salary 13 ID Number Position 1047 Sales Rep 1085 Sales Rep 1102 Sale Rep 1106 Sales Rep 1165 Support Staff 1473 Sales Rep 1486 Support Staff 1503 Support Staff 1519 Sale Rep 1529 Director 1601 Director 1603 Accountant 1675 Sale Rep 1828 Support Staff 2003 Director 2206 Sale Rep 2250 Director 2291 Support Staff 2292 Sales Rep 2416 Director 2528 Manager 2624 Manager 2742 Accountant 3004 Support Staff Employee Statistics Salary Job Satisfaction Salary Rank 45,855.00 46,063.00 45,700.00 47,572.00 30,176.00 45,993.00 32,666.00 32,757.00 45,674.00 88,197.00 89,691.00 69,389.00 49,617.00 35,230.00 83,240.00 42,383.00 92,700.00 35,242.00 44,854.00 77,846.00 70,125.00 73,564.00 62,263.00 30,982.00 mnm + + minin nmmnm mmm inn 16 Managers with > 4 Satisfaction Count Average Salary 18 19 Quartile Salary 24 26 Correlation 27 D 36 AB 3004 Support Staff 3083 Director 3161 Support Staff 3217 Sales Rep 3314 Sales Rep 3338 Support Staff 3402 Support Staff 34 3473 Support Staff 35 3638 Sales Rep 3652 Sales Rep 37 3782 Sale Rep 38 3808 Support Staff 39 3818 Sale Rep 3874 Support Staff 3877 Support Staff 42 3948 Support Staff 3969 Accountant 4239 Support Staff 4243 Support Staff 46 4269 Accountant 4398 Sales Rep 4541 Manager 4584 Support Staff 4652 Accountant 4771 Manager 4911 Director 4954 Director 30,982.00 84,937.00 32,709.00 40,449.00 45,983.00 36,942.00 33,852.00 33,501.00 48,005.00 48,706.00 45,927.00 31,632.00 49,575.00 34,154.00 34,775.00 37,404.00 65,492.00 32,107.00 35,316.00 68,365.00 41,130.00 72,862.00 30,701.00 66,009.00 74,767.00 81,082.00 97,159.00