Excel Problems #2 - Solutions

Excel Problems #2 - Solutions - Instructions 1 Change...

Info icon This preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Instructions: 1. Change column G in the table below (the Salary column) to Accounting formatting, with zero decimals. 2. Change column H (Date Hired) to the Short Date format. 3. Use the AVERAGEIF function in the Solutions Table to average salaries for Accounting Mgr's and Attorneys. 4. Use the COUNTIF function to fill in the Department Count Table to detail how many individuals are in each department. Sort the table by department in ascending order. Employee Name Department Title M/F Salary Date Hired Benson, Max Accounting Accounting Mgr M 42,000 $ 5/26/1994 Burnell, Dana Accounting Accounting Asst F 35,800 $ 1/20/1991 Solutions Table: Charney, Neil Legal Attorney M 89,000 $ 6/11/1987 Average Salary - Accounting Mgr 42000 Jacobson, Lisa Marketing Marketing Mgr F 42,000 $ 5/5/2002 Average Salary - Attorney 85500 Lew, Judy Accounting Accounting Asst F 36,200 $ 11/28/1995 Lugo, Jose Sales Sales Asst M 34,200 $ 4/10/1988 Department Count: Pellow, Frank Legal Attorney M 82,000 $ 9/16/1992 Accounting 3 Peters James Sales Staff Asst M 25,000 $ 12/9/2006 Legal 2 Price, Jeff Marketing Marketing Asst M 35,400 $ 2/10/1994 Marketing 5 Sandberg, Mikael Marketing Marketing Asst F 36,500 $ 10/24/1997 Sales 4 Shimshoni, Daniel Sales Sales Mgr M 43,200 $ 7/1/1993 Su, Min Marketing Marketing Asst F 35,000 $ 2/1/1999 Wheeler, Wendy Sales Sales Asst F 32,000 $ 3/15/1991 Wilson, James Marketing Staff Asst M 26,700 $ 8/17/2000
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Instructions: 1. Use Conditional Formatting to highlight all individuals in the Fine Arts department in a shade of green. Only the Department column needs to be formatted. 2. Adjust the column widths so that all of the data can be read. 3. Place filters on the column titles in Row 17. Sort the table by Date Hired, in ascending order (oldest to newest). 4. Enter today's date in cell K16. 5. In Column H, add the title "Work Days" in cell H17. Use the NETWORKDAYS function to determine the number of work days between the employee's start date and the current date for every employee. Place the answers for all employees in Column H. Ignore holidays with the function. 6. Make sure the formatting for the Data Table is professional, with a thick border on the outside and a single color for the entire table. Data Table: Date 3/27/2014 Last Name First Name Department Title Date Hired Work Days Lew Judy Dramatic Arts Professor 10/23/1979 8983 Bueno Janaina BioMedical Associate Professor 7/26/1980 8784 Johnston Tamara Student Services Academic Tutor 1/2/1985 7627 Adams Terry Media Studies Associate Professor 8/30/1985 7455 Evans John BioMedical Professor 2/20/1987 7070 Bankert Julie Interior Design Professor 3/5/1987 7061 Cavallari Matthew J. Student Services Academic Advisor 6/8/1987 6994 Haas Jonathan Fine Arts Professor 9/3/1988 6669 Arthur John BioMedical Associate Professor 1/15/1989 6574 Johnson Barry Advertising and Graphic Arts Professor 3/12/1989 6534 Core Debra Student Services Academic Advisor 5/4/1989 6496 Johnson David Administration President 1/2/1990 6323 DeVoe Michael Fine Arts Associate Professor 3/4/1990 6279 Canuto Suzana Interior Design Professor 5/1/1990 6238 Low Spencer Interior Design Associate Professor 5/2/1990 6237 Nartker Paula Interior Design Associate Professor 5/3/1990 6236 Oveson Scott Foundational Studies Associate Professor 5/4/1990 6235 Erickson Gail
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern