Excel Project II

PDF of Assignment

Next, you are going to continue with exploring features in Excel that will perform calculations and visual representations of your data. Excel is an ‘active’ application that will constantly update tables and graphs as new data has been added. You saw this with the first part of the project by adding new grades to a ‘student’ and having Excel automatically calculate their Grade Point Average (GPA).

You will look at 2 new functions: Conditional Formatting and IF.

• Conditional formatting – offers visual representations of data. For example, highlighting a students ‘status’ for those in the lower portions of class standings.


• IF statement: An advanced function in Excel, which will allow the application to perform calculations and graphing to visually represent data. For example, the “IF” feature will determine if a statement is ‘true’, then perform some action. For this example, we’re going to identify how many students are passing and how many need an ‘intervention’.


• Adding this ‘function’ has 2 purposes. First, to learn how to use the “IF, then” feature to add functionality to your Excel workbooks. Second, to learn the process of adding ‘functions’ to your workbook. Once you learn how to add ‘IF, you can use the same steps to add almost any function to a workbook.

Steps to complete project:

1. Read the overview above.


2. Watch the 2 screencasts on Conditional Formatting and IF.
   
   
   a. Conditional Formatting:
   http://www.screencast.com/t/c1qz7TY1SB0G
   
   
   
   
   
   
   
   b. IF statement: http://www.screencast.com/t/xAJQAglr
   
   
   
   
   
   
   
   
   


3. Open you Excel Interactive Grade book workbook.


4. Using the Conditional Formatting feature to add a color status to students assignment grades. As you see from my example, I have blue for A’s, green for B/C, and yellow for lower then C. Add the conditional formatting to all assignments.


5. Add a conditional formatting to the students overall grade column.


6. Using the IF statement. Add a Column to your grade book next to the students (ID) or name, label it “Status”.


7. Click on the first students ‘Status’ cell and insert the IF function. For students average is greater then 70, list their status as ‘Passing’. If students score is less then 70, list their status as ‘Intervention’.


8. Add a conditional formatting rule to change the color of the cell to highlight ‘Intervention’ students as a ‘red’ cell.