Excel Assignment 2 Overview This is an individual assignment, and you should create this spreadsheet on your own. Keep i

[answerhappygod]

Excel Assignment 2 Overview
This is an individual assignment, and you should create this
spreadsheet on your own. Keep in mind
that the tutorial is a learning tool and completion of the tutorial
is not the assignment. You should start
with a blank spreadsheet and build the assignment from scratch.
Create a spreadsheet that your
instructor could use to calculate final averages and letter grades
for a fictitious class. Each individual
grade will be recorded as points earned out of 100 total possible.
You should watch the tutorials for
Excel assignment 2 before beginning this assignment. The
spreadsheet should use a similar design and
features of the spreadsheet created in your Excel tutorial for
assignment two. More specifically, the
following requirements must be met:
1. Create a two-column table starting in cell A2 using the point
values below for pop quizzes, regular
exams, mid-term exams, final exams, and case studies.
Pop quizzes (5) 10% (drop lowest grade)
Regular exams (6) 30% (drop lowest grade)
Mid-Term 20%
Final 20%
Case Studies (2) 20%
100%
2. Create a two-column table starting in row 2 just to the right of
the table created in #1 above. This
table will include cutoffs for each grade level which are listed
below.
A 90
B 80
C 70
D 60
F <60
3. A minimum of 15 fictitious student records with grades (you make
up the names and grades)
entered for each student. Each student record should be on one row
and each grade item or calculated
value should be in one column similar to the tutorial. Begin with
student name in column A, two rows
below the tables created in 1 & 2 above.
4. Columns for each individual grade item and total points earned
in each category of grades (Pop
Quizzes, Regular Exams, Mid-Term Exams, Final Exams and Case
Studies). Columns for Final Average and
Letter Grade. For column headings use abbreviations or word wrap so
that columns are just wide
enough to accommodate the grades in each column. Center all column
headings.
5. Use absolute cell referencing and the percentages from the table
created in #1 above to calculate
total points earned for each grade category. Your formula should
drop the lowest grade for pop quizzes
and regular exams. Do not use the actual percentages in the
formula. Instead use the cell address
where the percent is stored. This design should be done in a way
that percentage changes in the table
would automatically be reflected in affected calculations.
6. Calculate a final average for each student.
7. Calculate a final letter grade for each student. Use an IF
statement and absolute cell referencing for
grade cutoff’s from the table created in #2 above (similar to what
was done in assignment 2 tutorials).
Reference the grade level cut-off levels from the table so the
cut-off levels for each letter grade can be
changed in the table and reflected in each individual letter
grade.
8. A class average (use the average function) for each individual
grade item, combined grade categories
(Pop quizzes, Regular exams, Mid-Term, Final and Case Studies), and
final average.
9. The highest (use max function) grade for each individual grade
item, combined grade categories (Pop
quizzes, Regular exams, Mid-Term, Final and Case Studies), and
final average.
10. The lowest (use min function) grade for each individual grade
item, combined grade categories (Pop
quizzes, Regular exams, Mid-Term, Final and Case Studies), and
final average.
11. In real world settings, you will often have to figure out how
to do things on your own. The
following requirement is not covered in the tutorials and you are
expected to figure this out on your
own. Use the COUNTIF function to count the number of A’s, B’s, C’s,
D’s, and F’s and put this data in a
table starting in row 2 just to the right of the table created in
#2 above.
12. Bold columns for total pop quiz points, total exams points,
total case study points, mid-term points,
final average, and letter grade.
13. Format all calculated values (not individual scores) as numbers
with one decimal point.
14. Save the file with your last name and first name as the file
name. Using the name John Smith as an
example, the Excel file name would be SmithJohn.xlsx. Upload your
completed assignment to the
Dropbox for assignment 2 in D2L by the due date. Verify that your
assignment is uploaded correctly.
This can be accomplished by opening the uploaded file to verify the
file opens properly.