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 tex

Excel homework

Excel Practical – Exercise 6
IF and NESTED-IF Statements

Don't use plagiarized sources. Get Your Custom Assignment on
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 tex
From as Little as $13/Page

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.