MIS 118 EXCEL HOME WORK
Deadline: Nov. 29, 2015
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
o Create a file in Excel that describes a grading system for a class.
Technology Features – Your results will include:
o One Excel workbook file with 3 separate, appropriately labeled worksheets.
o Tables, a graph, and a list of descriptive statistics.
o For this exercise, the correct values in each cell are important and will be graded.
o The graph will be graded on appropriate content, appropriate axis scaling, and clear labeling.
Content
o You should form 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. Their scores on the first 3 tests are as follows:
Test 1
Test 2
Test 3
Total
Rounded_Total
LetterGrade
Honor
Allen
89
78
89
Borlin
67
56
66
Catlin
78
76
76
Dorsey
56
34
45
Eugene
26
100
99
Finerran
99
68
97
Greco
78
87
88
average
o Using an Excel function, show each student’s TOTAL in an additional column labeled “TOTAL”, using the following weights for the test: Test 1 (30%), Test2 (25%) and Test3 (45%)
o Using an Excel function, show each student’s rounded total in an additional column labeled “Rounded Total”
o Calculate on a separate row the average for each test
o If a student’s rounded total is above “95”, 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”
o If a student’s rounded total is 90 or greater, they receive an “A”. Between 80 and 90 is a “B”, between 70 and 80 is a “C”, between 60 and 70 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 (USE THE HELP TO GET ASSISTANCE ON THIS) to indicate cut-off points between grades. Hint: You will need to place the “cut-off grade” values in cells somewhere on your worksheet…
o Below your table, create a graph showing the students’ rounded totals. Be sure to include appropriate labeling and spacing, so that the graph is non-repetitive and the scale is appropriate. Hint: A score of 100 is the highest possible…
o Below your table, create a graph showing the tests’ averages. Be sure to include appropriate labeling and spacing, so that the graph is non-repetitive and the scale is appropriate. Hint: A score of 100 is the highest possible…
o Create a chart that shows the number of A-students, B-students, C-students and so on.
o Have Excel color in Red all scores below 70
Objectives of this assignment
o Offer a chance to practice some advance functions in Excel
o Further practice of graphing and tabularizing information using Excel
Tips
o For all functions, create it once and use a fill-in procedure to complete the sheet for all students.
o For the “Grade” column, be sure to remember to use an absolute cell reference when determining comparison values.

MIS 118 EXCEL HOME WORK
Deadline: Nov. 29, 2015
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
o Create a file in Excel that describes a grading system for a class.
Technology Features – Your results will include:
o One Excel workbook file with 3 separate, appropriately labeled worksheets.
o Tables, a graph, and a list of descriptive statistics.
o For this exercise, the correct values in each cell are important and will be graded.
o The graph will be graded on appropriate content, appropriate axis scaling, and clear labeling.
Content
o You should form 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. Their scores on the first 3 tests are as follows:
Test 1
Test 2
Test 3
Total
Rounded_Total
LetterGrade
Honor
Allen
89
78
89
Borlin
67
56
66
Catlin
78
76
76
Dorsey
56
34
45
Eugene
26
100
99
Finerran
99
68
97
Greco
78
87
88
average
o Using an Excel function, show each student’s TOTAL in an additional column labeled “TOTAL”, using the following weights for the test: Test 1 (30%), Test2 (25%) and Test3 (45%)
o Using an Excel function, show each student’s rounded total in an additional column labeled “Rounded Total”
o Calculate on a separate row the average for each test
o If a student’s rounded total is above “95”, 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”
o If a student’s rounded total is 90 or greater, they receive an “A”. Between 80 and 90 is a “B”, between 70 and 80 is a “C”, between 60 and 70 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 (USE THE HELP TO GET ASSISTANCE ON THIS) to indicate cut-off points between grades. Hint: You will need to place the “cut-off grade” values in cells somewhere on your worksheet…
o Below your table, create a graph showing the students’ rounded totals. Be sure to include appropriate labeling and spacing, so that the graph is non-repetitive and the scale is appropriate. Hint: A score of 100 is the highest possible…
o Below your table, create a graph showing the tests’ averages. Be sure to include appropriate labeling and spacing, so that the graph is non-repetitive and the scale is appropriate. Hint: A score of 100 is the highest possible…
o Create a chart that shows the number of A-students, B-students, C-students and so on.
o Have Excel color in Red all scores below 70
Objectives of this assignment
o Offer a chance to practice some advance functions in Excel
o Further practice of graphing and tabularizing information using Excel
Tips
o For all functions, create it once and use a fill-in procedure to complete the sheet for all students.
o For the “Grade” column, be sure to remember to use an absolute cell reference when determining comparison values.