Question

1 Approved Answer

Posted on Jul 19, 2024

1. Format the whole spreadsheet. 2. In cell G52, enter the =DAVERAGE function to calculate the average salary of the employees who are in sales.

image text in transcribed

1. Format the whole spreadsheet. 2. In cell G52, enter the =DAVERAGE function to calculate the average salary of the employees who are in sales. 3. PDF page 1: Use Data Filter functions in Excel to set up a filter to find the female employees who are carriers. Put the output in a blank area below row 58 in the existing worksheet. Enter the function to calculate the average salary in the cell below the SALARY column in the output area. Print the output list into the first single page PDF in good format. Copy the average to cell G53. 4. PDF page 2: Repeat the Data Filter process to calculate the average salary of employees over 30 years old who are assigned to either route A or route B. Copy the output to an area below the worksheet. Print the output list into a second single page PDF in good format. Afterwards, enter the average to cell G54. 5. PDF page 3: Create yet another Data Filter to calculate the average salary of employees with more than two dependents who were hired after December 31, 2003. Print the output list into the third single page PDF in good format. Enter the average in cell G55. 6. PDF page 4: Add descriptions to the left of cells G52, G53, G54, and G55 that clearly identify each of the computed averages. Then print the worksheet area in Cells A52:G55 with worksheet frames into the fourth PDF. 7. PDF page 5: Sort the employee records by name in ascending order. Print the range in Cells A1:H20 into the fifth PDF in good format. G A B C D E F H 1 2 THE SHANK MILK COMPANY 3 4 Salesmen and Carriers 5 6 7 NAME SEX AGE POSITION DATE OF HIRE DEPEND SALARY ROUTE 8 Sully, Jane F 37 Sales 8/29/2004 2 48,100 A 9 Buckley, Thomas M 46 Carrier 8/14/2005 3 40,102 A 10 Rock, Troy M 55 Sales 10/30/2003 239,214 B 11 Jones, Roberta F 25 Sales 11/29/2001 2 44,320 C 12 Gomez, Miguel M 23 Carrier 11/14/2002 5 37,980 C 13 Wilson, Estee F 45 Sales 5/22/2004 3 35,900 B 14 ORourke, Eddie M 53 Carrier 8/9/2005 2 32,000 B 15 Sullivan, Robert M 41 Sales 3/19/2006 1 31,006 C 16 Girard, Robert M 29 Sales 1/20/2004 5 43,200 A 17 Charles, James M 28 Carrier 10/21/2002 5 49,000 A 18 Smith, John M 41 Sales 8/23/2000 4 54,231 C 19 Blake, Julie F 37 Carrier 12/24/2000 4 50,002 C 20 Timberlake, Mary F 35 Carrier 9/19/2003 1 46,892 A 21 Barnwell, Harry M 32 Sales 5/10/2004 2 40,038 A 22 Blakemore, Tom M 26 Sales 4/20/2005 337,654 B 23 Riley, Tommy M 29 Carrier 10/13/2003 6 42,078 B 24 Cooke, Bill M 54 Carrier 12/10/2005 4 30,010 C 25 Figgie, Gray M 33 Sales 12/15/2001 3 49,876 C 26 Hunt, Troy M 51 Sales 8/24/2000 2 60,300 A 27 Willson, Nancy F 37 Carrier 1/29/2003 3 45,345 A 28 Serrett, Gene M 45 Carrier 1/9/2004 4 39,845 C 29 Hensley, Barry M 34 Sales 7/29/2005 237,890 B 30 Weaver, Sally F 42 Sales 6/9/2005 5 38,750 C 31 Evans, Shirley F 58 Sales 9/20/2006 1 35,654 C 32 Thorne, Charles M 31 Carrier 4/19/2007 1 30,102 B 33 Baker, Lyle F 30 Sales 5/4/2007 3 32,567 C 34 Click, George M 22 Carrier 10/16/2006 3 33,209 A 35 Siple, Leona 25 Sales 2/2/2004 2 39,887 C 36 Byrd, Lucille F 27 Sales 11/3/2002 4 44,187 C 37 Martin, Danny M 26 Sales 9/8/2004 5 37,890 B 38 Ballew, James M 44 Carrier 6/24/2005 3 30,578 A 39 McCray, Jack M 33 Sales 12/31/2006 3 44,056 C 40 Shiley, Todd M 27 Carrier 4/21/2007 2 32,987 B 41 Hughes, Peggy F 28 Carrier 3/17/2005 3 31,467 B 42 Eagle, Frank M 34 Carrier 12/29/2003 4 34,380 C 43 McClure, Sam M 31 Sales 9/11/2006 5 29,845 A 44 Myers, Bill M 51 Sales 10/13/2005 3 34,521 A 45 Johnson, Blake M 46 Sales 1/30/2003 43,670 C 46 Fisher, Betty F 32 Carrier 10/26/2005 4 30,034 A 47 Stroud, Alice F 30 Sales 9/1/2005 4 42,442 A 48 Bell, Tommy M 28 Carrier 10/26/2003 3 34,523 C 49 Blackley, Frank M 34 Sales 4/9/2003 3 50,004 C 50 Shuey, William M 25 Sales 10/15/2004 2 39,873 B 51 8. PDF page 6: Sort the employee records by position in ascending order and within position by salary in descending order. Again, print the range in Cells A1:H20 into the sixth PDF in good format. 9. PDF page 7: Use the employee records sorted in Step 8 above, create a filter showing all males who are over 25 and who are in either route A or route B. Print the output list into the seventh PDF in good format. 1. Format the whole spreadsheet. 2. In cell G52, enter the =DAVERAGE function to calculate the average salary of the employees who are in sales. 3. PDF page 1: Use Data Filter functions in Excel to set up a filter to find the female employees who are carriers. Put the output in a blank area below row 58 in the existing worksheet. Enter the function to calculate the average salary in the cell below the SALARY column in the output area. Print the output list into the first single page PDF in good format. Copy the average to cell G53. 4. PDF page 2: Repeat the Data Filter process to calculate the average salary of employees over 30 years old who are assigned to either route A or route B. Copy the output to an area below the worksheet. Print the output list into a second single page PDF in good format. Afterwards, enter the average to cell G54. 5. PDF page 3: Create yet another Data Filter to calculate the average salary of employees with more than two dependents who were hired after December 31, 2003. Print the output list into the third single page PDF in good format. Enter the average in cell G55. 6. PDF page 4: Add descriptions to the left of cells G52, G53, G54, and G55 that clearly identify each of the computed averages. Then print the worksheet area in Cells A52:G55 with worksheet frames into the fourth PDF. 7. PDF page 5: Sort the employee records by name in ascending order. Print the range in Cells A1:H20 into the fifth PDF in good format. G A B C D E F H 1 2 THE SHANK MILK COMPANY 3 4 Salesmen and Carriers 5 6 7 NAME SEX AGE POSITION DATE OF HIRE DEPEND SALARY ROUTE 8 Sully, Jane F 37 Sales 8/29/2004 2 48,100 A 9 Buckley, Thomas M 46 Carrier 8/14/2005 3 40,102 A 10 Rock, Troy M 55 Sales 10/30/2003 239,214 B 11 Jones, Roberta F 25 Sales 11/29/2001 2 44,320 C 12 Gomez, Miguel M 23 Carrier 11/14/2002 5 37,980 C 13 Wilson, Estee F 45 Sales 5/22/2004 3 35,900 B 14 ORourke, Eddie M 53 Carrier 8/9/2005 2 32,000 B 15 Sullivan, Robert M 41 Sales 3/19/2006 1 31,006 C 16 Girard, Robert M 29 Sales 1/20/2004 5 43,200 A 17 Charles, James M 28 Carrier 10/21/2002 5 49,000 A 18 Smith, John M 41 Sales 8/23/2000 4 54,231 C 19 Blake, Julie F 37 Carrier 12/24/2000 4 50,002 C 20 Timberlake, Mary F 35 Carrier 9/19/2003 1 46,892 A 21 Barnwell, Harry M 32 Sales 5/10/2004 2 40,038 A 22 Blakemore, Tom M 26 Sales 4/20/2005 337,654 B 23 Riley, Tommy M 29 Carrier 10/13/2003 6 42,078 B 24 Cooke, Bill M 54 Carrier 12/10/2005 4 30,010 C 25 Figgie, Gray M 33 Sales 12/15/2001 3 49,876 C 26 Hunt, Troy M 51 Sales 8/24/2000 2 60,300 A 27 Willson, Nancy F 37 Carrier 1/29/2003 3 45,345 A 28 Serrett, Gene M 45 Carrier 1/9/2004 4 39,845 C 29 Hensley, Barry M 34 Sales 7/29/2005 237,890 B 30 Weaver, Sally F 42 Sales 6/9/2005 5 38,750 C 31 Evans, Shirley F 58 Sales 9/20/2006 1 35,654 C 32 Thorne, Charles M 31 Carrier 4/19/2007 1 30,102 B 33 Baker, Lyle F 30 Sales 5/4/2007 3 32,567 C 34 Click, George M 22 Carrier 10/16/2006 3 33,209 A 35 Siple, Leona 25 Sales 2/2/2004 2 39,887 C 36 Byrd, Lucille F 27 Sales 11/3/2002 4 44,187 C 37 Martin, Danny M 26 Sales 9/8/2004 5 37,890 B 38 Ballew, James M 44 Carrier 6/24/2005 3 30,578 A 39 McCray, Jack M 33 Sales 12/31/2006 3 44,056 C 40 Shiley, Todd M 27 Carrier 4/21/2007 2 32,987 B 41 Hughes, Peggy F 28 Carrier 3/17/2005 3 31,467 B 42 Eagle, Frank M 34 Carrier 12/29/2003 4 34,380 C 43 McClure, Sam M 31 Sales 9/11/2006 5 29,845 A 44 Myers, Bill M 51 Sales 10/13/2005 3 34,521 A 45 Johnson, Blake M 46 Sales 1/30/2003 43,670 C 46 Fisher, Betty F 32 Carrier 10/26/2005 4 30,034 A 47 Stroud, Alice F 30 Sales 9/1/2005 4 42,442 A 48 Bell, Tommy M 28 Carrier 10/26/2003 3 34,523 C 49 Blackley, Frank M 34 Sales 4/9/2003 3 50,004 C 50 Shuey, William M 25 Sales 10/15/2004 2 39,873 B 51 8. PDF page 6: Sort the employee records by position in ascending order and within position by salary in descending order. Again, print the range in Cells A1:H20 into the sixth PDF in good format. 9. PDF page 7: Use the employee records sorted in Step 8 above, create a filter showing all males who are over 25 and who are in either route A or route B. Print the output list into the seventh PDF in good format