Assignment #1
Submit your spreadsheet(WorkBook that contains the following parts of this assignment) to BlackBoard using the following file name format: LastNameFirstNameA1 i.e. if your name appears as Phuong Thanh on Black Board, then your workbook file name must be PhuongThanhA1.xlsx, Phuong being your last name or family name and your other name on Black Board is Thanh.
You are required to follow the rule below in your model:
• Separate numbers from formulas
There are two questions. You need to turn in one Excel workbook with two worksheets, one for each question. Name the first worksheet as Q1 and the 2nd as Q2.
Q1
1.Create the worksheet (shown at the end of this document).
2.Add yourself and two more employees to the spreadsheet (include hours worked and level).
3. Based on each person's level use nestedIF function to calculate Hourly Wage:
|
Level
|
Hourly Wage ($)
|
|
1.0~1.9
|
16.00
|
|
2.0~2.5
|
22.00
|
|
2.6~2.9
|
29.00
|
|
3.0 or higher
|
36.00
|
Levelis a value that has one decimal place.
Note: manually entering hourly wages in the "Hourly Wage" column will receive 0 for the whole assignment.
4.Fill in the Gross Pay column by using a formula which multiplies the Hours Worked times the Hourly Wage.
5.Using the built-in function 'sum', calculate the total gross pay.
|
Name
|
Hours
|
Level
|
Hourly Wage
|
Gross Pay
|
|
Lachance
|
40.5
|
1.1
|
|
|
|
Warner
|
39.5
|
3.2
|
|
|
|
McKaig
|
27.75
|
2.7
|
|
|
|
Shaw
|
38
|
3.5
|
|
|
|
Brooksbank
|
29
|
3.2
|
|
|
|
Darrach
|
15
|
1.2
|
|
|
|
Ruypers
|
14.75
|
2.1
|
|
|
|
|
|
|
Totals:
|
|
6.Sort the employees into alphabetical order.
7.Be sure your name is on the spreadsheet.
Q2. Short Description:
Spreadsheets are useful for many other things besides financial tasks, such as progress reports and for keeping track of descriptive data. For this assignment, you will create at least one table and an accompanying graphic visual that will help you write functions and learn simple programming techniques using advanced Excel features.
Requirements:
General
- Create a file in Excel that describes a grading system for a class.
Technology Features - Your results will include:
- One Excel worksheet with appropriately labeled worksheets.
- The correct formulas are important and will be graded.
Content
1 You should create a table on a worksheet titled "class list" that includes the names and test scores of your students. You have 7 students in your class, their names are: Allen, Borlin, Catlin, Dorsey, Eugene, Finneran, and Greco.
2 Also include your name to the list and scores on the first 3 tests are as follows:
|
Test 1
|
Test 2
|
Test 3
|
|
Allen
|
94.8
|
82.1
|
93
|
|
Borlin
|
67
|
56
|
66
|
|
Catlin
|
81.2
|
79.3
|
79.4
|
|
Dorsey
|
56
|
34
|
45
|
|
Eugene
|
85.5
|
100
|
99.4
|
|
Finerran
|
98
|
98
|
97
|
|
Greco
|
78
|
87
|
88
|
|
Yourname
|
*
|
*
|
*
|
(*) enter your scores here
Tasks
1) Using an Excel function, show each student's average in an additional column labeled "Average"
2) Using an Excel function, show each student's rounded average in an additional column labeled "Rounded Average" rounded to one decimal place. Make sure you round the value using the round function rather than format the value.
3) If a student's rounded average is 95 or above, he/she has received "honors" in the class. In an additional column titled "Honors", insert a function that will return the word "Yes" if they have received honors, otherwise would return the word "No".Make sure you put 95 in a cell, and in your formula you use the cell reference rather than value 95.
4) If a student's rounded average is 90 or greater, they receive an "A". Between 80 and 89.99 is a "B", between 70 and 79.99 is a "C", between 60 and 69.99 is a "D", and lower than 60 is an "F". Somewhere on your sheet, enter this information in cells. Create an additional column titled "Grade" and insert a nested IF function that returns the appropriate grade for each student. Use an absolute cell references in your nested IF function to indicate each cut-off point between grades. Hint: You will need to place the "cut-off grade" values in cells somewhere on your worksheet (for example, in range J2:J7 as shown in figure above) and use cell references such as J2 rather than values such as 90.You will lose at least 50% of the scores for nested IF function if you use values directly in the functions.
- Your work sheet should look like the figure above (but should be different as your table includes your scores).
|
A
|
B
Test 1
|
C
Test 2
|
D
Test 3
|
E
Avenge
|
F
|
G
|
H
|
|
Rounded
Average
|
Honors
|
Grade
|
|
Allen
|
94.8
|
82.1
|
93
|
89.97
|
90.0
|
No
|
B
|
|
Borlin
|
67
|
56
|
66
|
63
|
63.0
|
No
|
D
|
|
Catlin
|
81.2
|
79.3
|
79.4
|
79.96667
|
80.0
|
No
|
B
|
|
Dorsey
|
56
|
34
|
45
|
45
|
45.0
|
No
|
F
|
|
Eugene
|
85.5
|
100
|
99.4
|
94.96667
|
95.0
|
Yes
|
A
|
|
Finerran
|
98
|
98
|
97
|
97.66667
|
97.7
|
Yes
|
A
|
|
Greco
|
78
|
87
|
88
|
84.33333
|
84.3
|
No
|
B
|
|
John
|
77
|
88
|
82
|
82.33333
|
82.3
|
No
|
B
|