Lab 10: Using Conditional Statements in Excel

F. Using Conditional Statements in Excel

In this final section of Lab 10, you will use conditional statements so Excel can use your "Overall Percentage" to tell you how you are doing in the course. You will recall from earlier labs that a conditional statement takes the general form of:

If a certain condition is met then X, else Y.

Sometimes these are called IF/THEN/ELSE statements. Excel has a particular way, or syntax, of writing them, and you will use it to show a statement based on your current "Overall Percentage".

The basic format in Excel is:

=IF(LOGICAL TEST, VALUE IF TRUE, VALUE IF FALSE)

Another way of thinking about this is:

=IF(LOGICAL TEST, THEN, ELSE)

For example, if we assume the "Overall Percentage" is found in cell O32 and we want the text "Excellent Job" to show up if the person's percentage is greater than 90% but nothing to show up if it doesn't meet this criteria then we would write: =IF(O32>90%, "Excellent Job", ""). Suggestion: Use the overall percentage you would like to receive in the class.

Write two conditional statements using your "Overall Percentage" or another cumulative value (one that you have summed up in either columns "M", "N", or "O") and that you have calculated using formulas in Excel. Make certain text appear if the condition is true and other text appear if the condition is false. Remember, you must have quotes around the text in order for it to work in your function. Make sure to place a label to the left of your function so that both you and the TA know what it is doing. Placement of the function will also help with this.

Extra Credit: Nested Conditionals in Excel (4 points)

Grade Matrix
excel

Calculating your grade. Underneath "Overall Percentage" type "Course Grade". Refer to the "Grade Matrix" at right for determining the ranges for your grades.

We can do single conditional statements and take up several cells. The cell with the condition being met will then display your grade. For example, you could have a cell representing each letter grade and then some text in the event the condition is not met—something to the effect of "You do not have an A-".

The better way. However, we want your grade displayed in a specific cell and want only one function to make it happen. Therefore, we will create a nested function. Luckily, Excel makes this easy by keeping track of the parantheses you are using in pairs. You will want to create your function in the cell right below the cell that contains your calculated course percentage.

Start with the highest grade in order to simplify things. This way you can simply write > statements throughout. You will start your conditional statement as was done above, but for the false portion you will start another conditional nested within it. You will repeat this for each of the letter grades until you get to the last one in which you will simply type "D".

Here is an example: =IF(O32>.95,"A",IF(O32>.90,"A-",IF(...

Excel keeps track of parentheses for you. When you are done with your nested IF statement you will start closing it out with right parentheses. Look at the left side of the function and you will notice the left parentheses light up as you add right parentheses until it gets to the first one.

If you want to see your subtotal grades for each type of deliverable (optional) you can copy this cell and paste it below the subtotal percentages. This is a very useful feature in Excel, but be careful in relying on it as sometimes it doesn't work as expected. Double-check that the cell in the conditional statement is the sub-total percentage for the deliverable type.

3. Go to the Lab 10 WebQ and answer questions 6, 7, and 8.

Lab 10 Grades Spreadsheet

F. Using Conditional Statements in Excel

Extra Credit: Nested Conditionals in Excel (4 points)