Week 7 Practical Exercise

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 your current Grade Point Average. (GPA)

1. Copy the data from the table below into a new Excel Spreadsheet. Keep

the data as indicated on the columns and rows.

A B C

1 Subject Current Grade Grade Values

2 Math

3 Science

4 English

5 PE

6 Health

7 Computers

8 Social Studies

9

10 Total Values =

11

12 My GPA =

13

14 Grade Grade Value

15 A 4

16 B 3

17 C 2

18 D 1

19 F 0

2. Save the document as yourname_P6

3. Add 2 rows at the top of the table

4. Enter the title: GPA Calculator

a. Center the title across the table in row 1

5. In the column titled, Current Grade, enter grades to be calculated

a. Here are some examples of grades you can try:

i. Math: A Science: B English: A PE: C Health: B

Computers: B Social Studies: B

ii. Math: C Science: B English: C PE: C Health: A

Computers: A Social Studies: B

iii. Math: A Science: C English: A PE: B Health: A

Computers: C Social Studies: C

iv. Math: A Science: A English: A PE: D Health: C

Computers: B Social Studies: C

6. Use a Nested-IF Function to create the Grade Values. Use the Grade

and Values table to help you. You will need to use a NESTED IF

statement.

 Condition: IF Current Grade = “A”, then 4.0, else IF Current Grade

= “B”, then 3.0, else IF Current Grade = “C”, then 2.0, else IF

Current Grade = “D”, then 1.0, else IF Current Grade is anything

else it should be 0.

 Copy the formula to ALL cells in the column

o Be sure that ALL cells in the column have the SAME

formula

This video may help you:

https://www.youtube.com/watch?v=gYxpnmjoQds

7. In the cell next to the TOTAL VALUES=, insert a formula that calculates the

Total of all individual letter grade values found in the Column C.

a. You MUST use a FUNCTION and use ABSOLUTE CELL reference

based on the points in the Grade Value column

8. In the cell next to the MY GPA= cell, insert a formula that calculates the

AVERAGE of all the individual Grade Values. a. You MUST use a FUNCTION

b. SHOW 2 decimal places 9. In the same cell as above, use CONDITIONAL FORMATTING to color

the cell Green with a Bold White Font if the GPA is greater than 3.00 GPA.

10. Under the cell that calculates MY GPA, Insert an IF Statement to deliver one of the following messages:

If a student's GPA is greater than or equal to a 3.50, then have the cell

display HONOR ROLL! If the student's GPA is less than the 3.50, have

the cell display, Keep Trying!

FINISHING TOUCHES 11. Format the table to your liking

12. Change the tab name to GPA Calculator a. Color the tab GREEN

13. Add your name and student ID number to the header 14. 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, abc, 123, or the words “Excel”, “exercise”, “practical”, …