Assignment: Excel Practical

Assignment: Excel Practical
Assignment: Excel Practical
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 an 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 Note: Grade Values
15 A = 4.0
16 B = 3.0
17 C = 2.0
18 D = 1.0
19 F = 0.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
5. In the column titled, Current Grade, enter grades that you currently have.
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 an IF Function to create the Grade Values.
a. Use the NOTE: Grade Values to help you.
b. 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 = “F”, then 0.0, else 0.0
7. At cell B12, insert a formula that calculates the Total of all individual letter grade values found in the Grade Values Column.
8. In the cell next to MY GPA=, insert a formula/function that calculates the AVERAGE of all the individual Grade Values.
a. Make the value display 2 decimal places
9. Below the cell that calculates MY GPA, Insert an IF Statement for the following condition:
Condition: 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!
10. Use conditional formatting for the MY GPA result
a. If the GPA is less than 3.5, format the cell with a RED background and bold white letters
11. Format the rest table to your liking
12. Complete the document properties with your name, subject and tags (minimum of 3 separated by commas)