 |
 |
 |
 |
 |
CIS110 – Fictional Grade Spreadsheet
Objectives: In addition to what you have already learned in Excel, you will learn how to sort, apply various cell styles, apply conditional formatting, use nested Ifs (or lookup tables) and learn to use goal seek.
1. Create a spreadsheet to keep track of the following data (hint: you can copy the data from the webpage and paste it into a blank spreadsheet):
|
Name |
Test1 |
Test2 |
Test3 |
Final |
|
Marge Simpson |
65 |
78 |
98 |
78 |
|
Herman Hollerith |
89 |
91 |
62 |
99 |
|
Betty Rubble |
78 |
67 |
67 |
81 |
|
Buffy Summers |
98 |
89 |
89 |
77 |
|
Fred Flintstone |
80 |
56 |
78 |
23 |
|
Augusta Aida |
76 |
75 |
85 |
56 |
|
Jane Doe |
87 |
78 |
94 |
76 |
|
Suzy Snowflake |
99 |
83 |
95 |
98 |
|
Mister Bogus |
67 |
92 |
78 |
78 |
|
Roger Rabbit |
56 |
87 |
62 |
100 |
|
Herman Munster |
90 |
78 |
54 |
89 |
2. Requirements:
- a header with your name
- a footer with the date
- a title for the spreadsheet
- column and row headings
- use cell styles, borders and/or other formatting to make it look “pretty”
- the spreadsheet should fit on one page
3. You should sort the data alphabetically by name.
4. You need to calculate:
- Minimum Grade for student (no decimal places)
- Maximum Grade for student (no decimal places)
- Average Grade for student (two decimal places)
- Minimum Grade for test (no decimal places)
- Maximum Grade for test (no decimal places)
- Average Grade per test (two decimal places)
- Letter Grade for student
6. The student’s letter grade is based on the following:
=if(H3>89.5,”A”,if(H3>79.5,”B”,if(H3>69.5,”C”,if(H3>59.5,”D”,”F”))))
Caution: Your cell reference may be different.
Optional: Instead of using the nested IF, use a lookup table to calculate the letter grade:
Average Letter Grade
>89.5 A
>79.5 B
>69.5 C
>59.5 D
>0 F
7. Right now, Fred Flintstone is getting a D. The instructor has agreed to let Fred re-take his final (note that this is a fictional grade spreadsheet). Use goal seek to figure out what grade Fred needs to get on his final in order to get a C in the class.
8. Now that you are all experts in Excel, create a grade spreadsheet for your real CIS110 grades.