Question

Data WinaoW neip Edit View Insert Format Tools e Assignment10(4) (1 Insert Page Layout Formulas Data Review View Home General ▼ X Times New R ー▼ 11 ▼1 A-A- S.스. 杉転シ. Paste Total Number of Overtime Hours: A6 Hourly Rate: S 6.64 Overtime Rate: S 8.23 Auerans Hours Workd by Males in Dept No 2: Total N mber of Overtime Hours: 36 Total Overtime Pay: 10 Emplovees 11 Darrel 12 Sheltom, Kent 13 Diffce, Kois 4 Stull, Marleoe 15 Crenshaw, Sheiln Gender Dept No Department Hours WorkedOvertime Hours NonOvertime Daiy Daily Pay 26.56 53.12 39.84 10 69.58 61.35 S46.48 Denton, Susie 17 Huntingtm, Mike 18 HalL, Mike 19 İSmith,Brian 20 Hill, Pegay Joan 21 |Hill 22 White, i 51.35 69.58 10 S Whitchead, Babby 24 AdkinsDorinda 25 Boucbe:, Leslie 26 Clayton, Rex 27 Trice, 28 Wehh, Willan W 61.35 86.04 102.30 77.81 46.48 61.35 53.12 39.84 46.48 53.12 77.81 14 30 Savoie, T Webb, Patsy 32 HTTons iammons, Lary 33 L, T Tibbetts, 35 Piersan, Tony 36 Pierce,S 123 Sheet1Sheet2+ Ready

PLEASE HELP!!!!

1) CONDITIONAL FORMATTING - Add conditional formatting to the Hours Worked field data so that any hours worked greater than 8 will show in white font with a dark fill.

2) DATABASE FUNCTION - Create a database function in cell E5 that calculates the average hours worked by males in Dept. No. 2. If criteria are needed, start in cell E1.

3) VLOOKUP - In the Department field (column D) use the VLOOKUP function to place the appropriate department names in the Department field for each employee. Begin the table in cell H1. Dept. No. 3 is Computer; Dept. No. 2 is Sales; and Dept. No. 1 is Manufacturing.

4) IF - In the Daily field use the IF function to indicate the word Overtime if the hours worked is greater than 8, the word None if the hours worked is zero, and if the hours worked is greater than zero but 8 or less multiply the number of hours worked by the hourly rate in cell B2. Copy this formula down the Daily column for each employee.

5) CHART - Create a 3-D pie chart showing the breakdown of total Hours Worked into total Overtime Hours and the NonOvertime hours located at the bottom of the database listing, row 37 columns E, F, G. The pie chart should show the values, the legend should indicate Overtime Hours and NonOvertime, and the chart title should be Total Daily Hours. Place this chart so that it will fill cells K1:R16. (Hint: When selecting the data for this chart remember that pie charts show parts of a whole so there’s no reason to include a total if you are displaying the parts making up that total.)   [In spreadsheets, does it make sense to put totals at the bottom of the database?]

6) DATA TABLE - Create a Data Table that shows the average Daily Pay by gender for each department number. Start the criteria in cell E40 and the data table in cell E44.

7) SUBTOTALS - Create a Subtotal showing the total hours worked by department and the total overtime hours worked by gender within each department.   Change to Outline View 3. [Which department works the most hours and do you see something odd about the overtime hours?] Copy the subtotal information to Sheet 2 using Copy/Paste Special Values (previously undisplayed/hidden data will appear when copied to Sheet 2).

Remove the Subtotaling on Sheet 1 using the Remove All button before continuing.

8) GOAL SEEK - In cell E7, place a formula that will multiply the Overtime Rate (B3) by the Total Number of Overtime Hours. Perform a Goal Seek to determine the overtime rate necessary to achieve $400 in total overtime pay. Leave the goal seek in place so that it can be graded.

9) EXTRACT (Advanced Filter) - Sort the database by the employees' names in ascending order. Extract just the name and the department number (Dept. No.) of those who worked more than 8 hours.   Start the criteria in cell A40 and the extract output in cell A44.

10) DATA TABLE – on SHEET 3 create a One-Variable Data Table that will take the ODD numbers from 1 to 55 and will show each of the following independent calculations: multiply by $6.64; divide by 3.245; add $8.23 to each number; subtract 4 from each number.

11) Put your name in the Header and submit your file (instructor’s flash drive or Blackboard).

12) If required by your instructor, print the Sheet 1 worksheet (including chart) landscape orientation, showing the worksheet frame (row & column headings), fit-to-one page.

If required, print the contents of Sheet 2 and Sheet 3.

Answer 1

Answer 1)

Hourly rate overtime rate 6.64 8.23 Average hour worked by males in dep no 2 Total number of overtime hour Total overtime pay DailyPay Employee genderdept no department hour worked overtime non overtime Daily 26.56 53.12 39.84 10 69.58 61.35 46.48 33.2 86.04 61.35 69.58 77.81 19.92 10

2 3 .3 .0 2. 8 4 3-84-82 160 7 6 13 9 637 5 1 4 6 534579 8 30867888 888878 00146301000035 8 2 1 2|2|313|232 13 2 13 MFFMMMFMFMFMMM

Below is the step for answer 1

Answer 2)

Hourly rate overtime rate 6.64 8.23 Average hour worked by males in dep no 2 Total number of overtime hour Total overtime pay

formula:  =AVERAGEIFS(E11:E36,B11:B36,"M",C11:C36,2)

Answer 3)

Formula:  =VLOOKUP(C11,$L$4:$M$6,2)

D11 0 Hourly rate overtime rate 6.64 dept no department 1 Manufacturing 2 Sale 3 Computer Average hour worked by males in dep no 2 5.4 Total number of overtime hour Total overtime pay oEmployee gender dept no department 1 A hour worked overtime non overtime Daily DailyPay 2 Sale 1 Manufacturing 3 Computer 3 Computer 1 Manufacturing 1 Manufacturing 2 Sale 2 Sale 3 Computer 3 Computer 1 Manufacturing 3 Computer 2 Sale 26. 56 53.12 39.84 3 C 4 D 69.58 61.35 46.48 33.2 86.04 61.35 69.58 77.81 19.92 6 F 7 G 8 H 01」 1 K 2 L 3 M 10

DailyPay Employee gender dept no department hour worked overtime non overtime Daily 2 Sale 1 Manufacturing 3 Computer 3 Computer 1 Manufacturing 1 Manufacturing 2 Sale 2 Sale 3 Computer 3 Computer 1 Manufacturing 3 Computer 2 Sale 2 Sale 3 Computer 1 Manufacturing 3 Computer 1 Manufacturing 2 Sale 3 Computer 2 Sale 1 Manufacturing 26.56 53.12 39.84 10 69.58 61.35 46.48 33.2 86.04 61.35 69.58 77.81 19.92 10 61.35 86.04 102.5 77.81 46.48 61.35 53.12 39.84 14

Answer 4)

Formula:  =IF(E11>8, "Overtime", IF(E11=0, "None", 6.64*E11))

XVxF(E11>8, "Overtime", IF(E11-0, "None", 6.64 E11) H11 hour worked overtime non overtime Dai DailyPay 0 Employee gender dept no department 2 Sale 1 Manufacturing 3 Computer 3 Computer 1 Manufacturing 1 Manufacturing 2 Sale 2 Sale 3 Computer 3 Computer 1 Manufacturing 3 Computer 2 Sale 2 Sale 3 Computer 1 Manufacturing 3 Computer 1 Manufacturing 2 Sale 3 Computer 2 Sale 1 Manufacturing 1 A 2 B 3 C 4 D 5 E 6 F 7 G 8 H 9I 26.56 26.56 53.1253.12 39.84 8 39.84 0 None 8 Overtime 8 Overtime 10 69.58 61.35 746.48 46.48 33.2 86.04 61.35 69.58 8 Overtime77.81 3 19.9219.92 33.2 Overtime 8 Overtime 8 Overtime 1 K 22 L 3 M 10 5 0 None 8 Overtime 8 Overtime 8 Overtime 61.35 86.04 102.5 8 Overtime77.81 7 46.4846.48 61.35 53.12 39.84 5 O 14 8 R 7 Overtime 53.12 39.84 9 1 U