Lab 10
Question 2 Question 3 Question 4 Question 5 Question 6 Question 7
Employee Number Name Satisfaction Job Status Evaluation Satisfaction Bonus Evaluation Bonus Satisfaction Bonus Data (For Q6)
11 0 $0.00
3 $10,000.00
5 Question 8 Question 9 Question 10 Question 11 Question 12 Question 13 5 $15,000.00
Hire Date Date of Birth Location Salary Tax Comment Tax Rate 7 $25,000.00
Marks 9 $50,000.00
1) Name cells A3, C3, E3 and E7 to whatever appears above that cell. Ie A3 would be named "Employee_Number". Name the follownig ranges, J3:K8 2 12 $100,000.00
"Satisfaction_Bonus_Your INITIALS", J11:K14 "Evaluation_Bonus_Your INITIALS, J17:L21 Tax_and_Comment_Data_Your INITIALS. Finally, name the range
A2:I64 (This is on the data worksheet) to Employee_Data_Your INITIALS. For example, I would name A2:I64 "Employee_Data_RS". All functions must use range names! Evaluation Bonus Data (For Q7)
2) In cell B3 create a vlookup function that displays the name of the employee based on the Employee # entered in A3 and the info on the "Data" worksheet 1 Poor $0.00
3) In cell C3 create a vlookup function that displays the Satisfaction of the employee based on the Employee # entered in A3 and the info on the "Data" worksheet 1 Satisfactory $20,000.00
4) In cell D3 create a vlookup function that displays the Job Status of the employee based on the Employee # entered in A3 and the info on the "Data" worksheet 1 Good $30,000.00
5) In cell E3 create a vlookup function that displays the Evaluation of the employee based on the Employee # entered in A3 and the info on the "Data" worksheet 1 Excellent $50,000.00
6) In cell F3 create a vlookup function that outputs the Satisfaction Bonus for the employee based on the Satisfaction displayed in C3 and the info in the Satisfaction Bonus Data. For example, if they have a satisfaction of 3 they would get a bonus of $10,000 and a satisfaction of 2 would get them $0 1
Tax Rate and Comment Data (For Q12-13)
7) In cell G3 create a vlookup function that outputs the Evaluation Bonus for the employee based on the Evaluation displayed in E3 and the info in the Evaluation Bonus Data. For example, if they have an evaluation of Excellent they would get a bonus of $50,000 1 $0.00 Great 12%
$25,000.00 Good 18%
8) In cell B7 create a vlookup function that displays the Hire Date of the employee based on the Employee # entered in A3 and the info on the "Data" worksheet 1 $35,000.00 OK 22%
9) In cell C7 create a vlookup function that displays the Date of Birth of the employee based on the Employee # entered in A3 and the info on the "Data" worksheet 1 $50,000.00 Meh 25%
10) In cell D7 create a vlookup function that displays the Location of the employee based on the Employee # entered in A3 and the info on the "Data" worksheet 1 $75,000.00 Yuck 30%
11) In cell E7 create a vlookup function that displays the Salary of the employee based on the Employee # in A3 and the info on the "Data" worksheet 1
12) In cell F7 create a vlookup function that outputs the co
ect Tax Comment for the employee based on the Salary entered in E7 and the info in the Tax Rate and Comment Data. For example, if they have a Salary of $30,000 their comment would be "Good", $45,000 gets them "OK" 1 Average Salary of all employees
13) In cell G7 create a vlookup function that outputs the co
ect Tax Rate for the employee based on their Salary entered in E7 and the info in the Tax Rate and Comment Data. For example, if they have a Salary of $77,000 they would be at 30%, $20,500 gets them to 12% 1
14) In cell J24 use a simple worksheet reference from the Data worksheet to display the average salary of all employees calculated in I65 on the "Data" worksheet 1
15) On the Image worksheet, use wordart to add your full name across the entire page. Set the font size to anything over 100. Add a glow text effect of your choice 1
16) On the Image worksheet, fill the range A1:AZ80 sheet with a custom colour of your choice. 1
17) On the Image worksheet, add at least two pictures of your choice but make sure they are exactly the same size. Add an artistic effet to one and recolour the next. 1
Lab Mark /18
As the boss of ACME Toilet Co. you need to create a worksheet that gives you quick access to information specific to each individual employee by simply typing in the employee number into cell A3.
When done, save as "YourFirstName YourLastName Lab 10" and submit via DC Connect
ACME Toilet
Test your functions by using different employee numbers
We flush
Everything Down!
Company
Data
Employee # Name Hire Date Date of Birth Satisfaction Location Job Status Evaluation Salary
115 Abe 02,Oct,2010 06,May,1959 6 U.S. FT Good $63,583.00
85 Amanada 23,Feb,2011 11,Sep,1975 12 Canada FT Good $80,327.00
125 Ba
11,Aug,2012 25,Jul,1973 3 U.S. FT Excellent $84,044.00
65 Bob 16,Oct,2009 08,Aug,1977 11 Brazil FT Excellent $50,306.00
11 Brianna 19,Jun,2012 11,Sep,1967 2 U.S. FT Good $47,890.00
47 Catherine 17,Aug,2011 16,May,1965 1 Mexico PT Excellent $74,939.00
37 Charlie 21,Sep,2010 10,Jun,1979 10 U.S. FT Good $42,782.00
55 David 23,Aug,2011 01,Jul,1974 1 Canada PT Satisfactory $44,659.00
103 De
a 10,Feb,2012 14,Mar,1975 10 U.S. PT Satisfactory $72,827.00
23 Dex 03,Jul,2010 23,May,1957 8 Brazil FT Good $55,183.00
79 Dirk 09,Oct,2011 08,Sep,1970 4 U.S. FT Excellent $75,017.00
29 Don 22,Jul,2011 24,Feb,1979 5 U.S. PT Excellent $75,284.00
31 Doug 25,Aug,2012 15,May,1964 12 U.S. PT Satisfactory $36,409.00
13 Fran 14,Jun,2011 29,Oct,1975 12 Canada FT Poor $49,169.00
15 Frank 17,Jul,2009 15,Nov,1981 1 England FT Poor $49,274.00
67 Garth 29,Sep,2012 09,Apr,1973 7 Canada FT Excellent $52,306.00
117 Gary 03,May,2011 27,Jan,1972 5 U.S. FT Excellent $65,075.00
121 Genny 20,Jun,2011 06,Apr,1960 9 U.S. FT Good $74,302.00
35 Hank 06,May,2012 24,Sep,1960 7 Canada FT Satisfactory $37,601.00
83 Ha
y 21,Jul,2009 02,Apr,1963 1 U.S. PT Good $79,113.00
89 Heather 19,Sep,2009 17,Aug,1963 12 Mexico FT Excellent $83,635.00
91 Heather 02,Jul,2011 28,Jun,1963 6 Brazil FT Good $36,974.00
105 Iggy 08,Mar,2012 13,Jul,1979 12 U.S. FT Satisfactory $74,263.00
59 Jack 17,Sep,2010 02,Sep,1970 3 Canada FT Good $45,821.00
81 Jasper 28,Apr,2011 14,Jul,1975 4 U.S. PT Poor $76,061.00
109 Jay 22,Sep,2009 21,Oct,1962 10 Canada FT Good $45,973.00
95 Jill 28,May,2011 11,Feb,1965 8 U.S. FT Satisfactory $56,453.00
71 Jimmy 14,Jan,2011 10,Mar,1965 6 U.S. FT Good $63,539.00
53 Jocelyn 06,May,2010 07,Jul,1969 4 U.S. FT Good $43,681.00
33 John 22,Jun,2012 27,Dec,1970 5 U.S. FT Poor $36,951.00
43 Krum 15,Aug,2011 22,Sep,1968 11 Canada FT Excellent $66,282.00
25 Lanny 02,Aug,2011 01,Dec,1955 7 Canada FT Satisfactory $57,470.00
41 La
y 27,Sep,2011 05,May,1975 5 U.S. FT Good $54,779.00
97 Lester 14,Jul,2012 16,Nov,1961 7 Canada FT Excellent $56,944.00
77 Linda 15,Oct,2012 22,Mar,1958 9 Canada FT Excellent $72,410.00
93 Louise 24,Oct,2011 26,Feb,1979 1 U.S. FT Satisfactory $42,979.00
21 Lucy 24,Feb,2012 25,Dec,1964 2 U.S. PT Good $51,673.00
57 Mack 21,Nov,2011 17,Aug,1961 9 England PT Good $45,255.00
45 Maddie 15,Jul,2010 18,Nov,1972 9 U.S. FT Good $69,601.00
119 Marnie 08,Aug,2010 06,May,1962 5 Canada PT Good $66,740.00
3 Martin 09,Jul,2011 27,Feb,1955 11 U.S. PT Good $37,207.00
61 Mary 25,May,2012 17,May,1980 12 U.S. FT Good $49,361.00
75 Matt 18,Feb,2010 21,Jul,1963 3 U.S. FT Good $70,653.00
27 May 26,Aug,2009 15,Jan,1972 7 U.S. FT Good $74,394.00
5 Michael 13,Oct,2012 06,Nov,1960 5 Mexico PT Poor $40,912.00
111 Missy 27,Apr,2010 24,Mar,1975 6 U.S. FT Poor $47,333.00
99 Nadine 30,Dec,2010 15,Sep,1965 9 England FT Excellent $57,081.00
39 Nigel 23,Jan,2011 02,May,1979 6 U.S. FT Poor $51,826.00
113 Olivia 08,Sep,2011 21,May,1972 5 U.S. FT Excellent $48,985.00
107 Pauline 19,Jan,2012 28,Dec,1973 7 Brazil FT Excellent $42,622.00
17 Pravin 06,Jun,2012 07,Sep,1979 4 Canada FT Satisfactory $50,595.00
123 Quorin 09,Jun,2012 06,Apr,1960 11 U.S. FT Excellent $77,466.00
1 Reggie 17,Oct,2009 18,Sep,1969 5 Canada FT Poor $35,513.00
19 Rhonda 27,May,2010 27,Oct,1958 1 U.S. FT Good $50,688.00
7 Rosie 14,Aug,2011 24,Oct,1955 1 Brazil FT Poor $44,621.00
51 Shannon 22,May,2010 05,Jun,1972 1 U.S. FT Excellent $43,061.00
87 Stan 02,Jul,2011 28,Jun,1958 10 U.S. FT Satisfactory $83,404.00
9 Tamara 23,Aug,2009 10,Aug,1969 11 U.S. FT Satisfactory $47,305.00
69 Tammy 12,Feb,2010 02,Jun,1968 10 U.S. FT Good $58,250.00
63 Tara 24,Jan,2012 01,Nov,1968 5 U.S. FT Good $49,373.00
49 Taya 13,Sep,2009 08,Apr,1966 7 Brazil FT Excellent $81,095.00
101 Timothy 05,Oct,2011 19,Jul,1970 2 Canada PT Excellent $71,085.00
73 Vic 24,Feb,2012 23,Dec,1970 4 U.S. PT Poor $68,372.00
Average Salaries $57,758.35
Image