Excel homework
Excel Practical – Exercise 6
IF and NESTED-IF Statements
Note: If the function or method was not covered in class or in the
textbook, it should not be part of your answer. DO NOT go to the
internet for solutions.
You will be constructing a spreadsheet that you can use to calculate Test
Scores and letter grades.
1. Copy the data from the table below into an Excel Spreadsheet. Keep the
data as indicated on the columns and rows.
A B C D E F
1 Student Superman Thor Peggy
Carter
Hulk Black
Widow
2 Test 1 87 91 92 94 68
3 Test 2 57 56 97 93 95
4 Test 3 90 83 48 90 93
5 Test 4 56 78 84 67 63
6 Test 5 84 92 57 92 97
7 Test 6 70 66 83 85 88
8
9 Average =
10 Grade =
11 Message =
12
13 Grade Scale Grade
14 90 100 A
9 80 89 B
16 70 79 C
17 60 69 D
18 0 – 59 F
19
2. Save the document as yourname_P6
3. Add 2 rows at the top of the table
4. Enter the title: Test Score Calculator
a. Center the title across the table
CALCULATIONS
5. In the row titled, Average, USE the AVERAGE function for each
student
a. Use the Fill handle to copy the formula to all other cells in the
row.
Continue on next page
6. Show the grade for each student
i. You MUST use a NESTED IF Function to enter a letter grade
for each student.
b. Use the Grade Scale table to help you
1. You MUST use the Grade Letter in column B for the
letter grade to be entered in row 10.
c. You MUST use ABSOLUTE CELL REFERENCE
1. Use the Fill handle to copy this formula to all of the
other cells in the row.
7. In the row titled Message,
i. Use a NESTED-IF Function to enter one of the following
messages based on the letter grade show in the Grade row
IF the grade = A, then the message is Excellent
If the grade = B or C the message Good Work
Anything else the message should say Needs
Improvement
b. Use the Fill Handle to copy the formula to all the
other cells in the row.
CONDITIONAL FORMATTING
8. Use conditional formatting for the AVERAGE result
a. If the Average is less than 70, format the cell with a RED
background and bold white letters
FORMATTING
9. Format the rest table to your liking
10. Format the title to your liking
11. Change the page orientation to landscape
CHARTING
12. Create a 3-D Column Chart showing the average for each student
13. Move the Graph to a new sheet
a. Call the new sheet Averages
b. Add a title to the Chart
i. You create one
c. Add Axis Titles to both Axis
d. Add data labels to show each score
e. Color the tab RED
Continue on next page
FINISHING TOUCHES
14. Add your name and student ID number to the header
15. Name the worksheet with the table TEST CALCULATOR
a. Color the tab: Blue
16. Change the document properties
– Author, Subject and TAGS (keywords use a minimum of 3 words
separated by commas).
o Tags should NOT be your initials, Excel, exercise,
Be sure that you can change the test
scores to achieve different averages.
This must work.
