Subtitles of the Movie

The Conditional Statement is an extremely popular Function that is contained within Microsoft Excel. As a result, it's imperative that you understand it's complexities in preparing for the Examination. Be prepared to have a Conditional Statement come up. Keep in mind it will be more of a complex, rather than a simple statement. Let's go through a simple statement and then go through the complexities. First, when we talk in terms of a Conditional Statement, we are talking in terms of an IF statement. IF is the Conditional Statement. Now notice that IF appears outside of the parentheses. Typically, this connotates an actual Function. The entire listing of this function is then referred to as a Formula. The Function is usually Coding, the Formula is what I want to do. Let's read the statement: If B5 equals blue. Now, notice blue is in quotes. I had to put blue in quotes to tell Excel, look, this is going to be something that I've typed in. Don't confuse it with the name of a Function. Functions do not have the quotes around it Ð anything I type Ð the data does. If B5 equals blue, what would you like me to do? I'd like you to place a number 1. Now, notice between the Condition or the Criteria is an actual comma. State the condition, put in the comma, tell me what to do when it's true, comma, tell me what to do when it's false. That is the Syntax of a Conditional Statement. In this case you'll notice that B5 is indeed blue. So, the result, put in a number 1. If I go back and change this to green, then you will notice that it says Not Assigned, because it no longer meets that condition. If I go down to the second result, you'll notice that I changed this. In this case I said: If B6 equals green, then put in a 2. If not, put in Not Assigned. Here's the difficulty though. I would have to go to each individual cell to revise this particular formula. That would get very tedious. Instead, we'll do a Nested Statement, and that's the term you want to be aware of for the Examination: How do I do a Nested Conditional Statement? In this case I'm going to put in the equal sign and again type in IF. Always start with the parenthesis; this is what contains the information for the Function itself. Notice the Help Menu came up. It refers to the criteria as Logical Test. So, what am I looking for? IF B5 equal, and in this case I'll say green, again, putting in quotation marks. I'm putting in a comma and then I'm going to tell it what to do when it's true. In this case, a 2. I now need to up to in a comma and tell it what to do when it's false. In this case, I'll say Not Assigned, once again including it in brackets. But what if I don't want to just put in Not Assigned. I actually want to put in another Condition. Well, instead of putting in Not Assigned in the false section, I'm actually going to start with another IF statement. I'm again putting in the brackets and I'm again going to put in the Condition of IF B5 equals, but this time I'll say: If it equals blue. If it equals blue I'm now going to say put in a number 1. If it's not blue, I can either go into another conditional statement starting with the IF one more time, or I can tell it what to do if neither green nor blue is true. And in this case I am going to put in Not Assigned. What I'd like you to keep in mind is that you can nest together a good number of these statements. Do remember that if you have too many we'll go to Code. But in this, if B5 equal green put in a 2. If B5 equal blue put in a 1. If neither of those conditions are true, then put in Not Assigned. I have two conditions so I have two parentheses at an end, and I'm once again going to go ahead and hit Enter. Now, remember our Statement: it's dependent on B5. If I put in blue you'll notice it's a 1; if I put in green, it becomes a 2; if I put in yellow, which I did not account for, it's Not Assigned. In understanding the Conditional Statement you'll need to test your own logic. I have put a sample Worksheet for you to do that. The idea here is to automatically assign a letter grade to scores on an examination. Notice in the Conditional Statement: IF B6 is greater than 89, put in an A; IF B6 is greater than 79, put in a B; IF B6 is greater than 69 a C; and if B6 is greater than 59, put in a D. If none of these conditions are true, then it's an F. I have put a copy of this Worksheet into the Work File. I encourage you to open up Excel and try this exact example again. Remember, it's important to understand the Formulas, it's important to understand the Nesting Component of the Formula.