02 Jul computer assignment
Question Description
ilter, List and Sorting: (18pts)
Open SortFilterData.
In Wages worksheet:
a. Display only Winter employees from the Tours department. (1pt)
b. Sort the results by Status alphabetically and then by Hourly Wage with the most expensive employees first. (3pts)
In the Roster worksheet:
a. Create a table from the data in A1:H112 and add yourself to the list with your LastName and FirstName; You work in Marketing, 1st shift and you make $33,333; you were hired yesterday, choose your own marital status. (1pts)
b. Display the Roster employees who make anywhere from $30,000 thru $40,000.Sort the results by marital status in descending order and if there are duplicates, then by salary in descending order, and if there are still duplicates, by Last Name in descending order. Print (or upload a screenshot) and label your printout. (3pts)
c. Add a total row to the table that computes ONLY the Average salary of the employees displayed in the previous step. (2pts)
d. In B120 enter the label Last Name and in C120 enter the label Salary. In C121 create a function that looks up the name entered in B121 and returns their salary in C121. Test the function by entering Metzger in B121. (2pts)
e. Print and label your printout (or upload a screenshot).
In the TV Shows worksheet:
a. Use an Advanced Filter to filter the data to A40 in order to display the TV Shows that were 60 minutes in length OR were a Comedy that received a Rating of at least 4.(3pts)
5. In the Cruises worksheet
a. Sort the data by Accommodation category in Ascending Order (1pt)
b. Create Subtotals below the data for each Accommodation category that totals the # of Rooms Available and the Revenue. Display only the 2nd level detail so that only category totals and the Grand Total display. (2pts)
6. Save and upload the file SortFilterData to D2L!
School Stats– 22pts
Open School Stats. Save the file as XXX School Stats. Create a header in each sheet (current and new sheets) of the workbook that includes your name, centered. (1 pt)
Create a table from the data A3:G20. Row 3 should be a header row. Add data validation to column G that only allows choices of Freshman, Sophomore, Junior or Senior. Add an appropriate alert message that warns the user if they enter an invalid response. Add a new record into the table for yourself in row 20 – (you are a Junior Business major) make up your points data. (4 pts)
Create a pivot table in a new sheet, named Pivot Data, based off the data in range A3:G20. The columns should be the Year in School, the rows should be the Majors, and the data should be the average GPA. (3 pts)
Format the data so that GPA scores only show two decimals. (1 pt) Manually adjust the columns so that the Sophomore data is to the immediate right of Freshman data and so forth. (BONUS pt)
Add Last Name as the inner field row label after Major. (1 pt)
Format the PivotTable as Light Orange, Pivot Style Medium 7. Remove the grand totals for rows. Remove the column and row label headings and PRINT this sheet. (or upload a screenshot) (3 pts)
Create a pivot chart in a new sheet, named Pivot Chart, based off of the pivot table data. Modify the data via the PivotTable created in step 3 by adding slicers for the Year in the School & Major. The chart should only display Junior and Senior data for the Business and Liberal Arts majors. (see illustration below) Format the Year in School slicer as Ice Blue, Slicer Style Dark 1 and the Major slicer as Lavender, Slicer Style Dark 4. (6 pts)
pivotchart
Name the chart title, “Average GPA by Major by Year in School” and format the chart as Chart Style 5. Add a data table with legend keys below the chart, then PRINT the chart and the related PivotTable (or upload screenshots). (3 pts)
Save and upload the file to D2L.
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Wridemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.