Alex has been called in by a school to help them with the new invoicing system that they're trying to implement. Because it is brand new, they're testing it all in Excel first. Now, what the school has decided to do is give more incentives to parents who are more involved in the school by offering them discounts. For example, if they have more children enrolled they get a sibling discount. If they help out more in the school, they get a volunteer discount. Now, in order to do this we're going to need to use some conditional logic. Conditional logic allows the workbook to respond differently to different situations. For example, if I have more than one student enrolled, then there is a sibling. Otherwise, there is not. Now, one function that can help us fantastically with conditional logic is the "IF function". The "IF function" is structured like this: we start with our equals, then IF, and open our brackets, it then expects three arguments in the brackets. The first argument is a logical test. Now, a logical test compares two values using a logical operator. So, for example, I could check if X is equal to Y or if X is less than Y. There are actually six logical operators you can choose from: equals, less than, greater than, less than or equal to, greater than or equal to, and finally not equal to. And you will have to ensure that you've used one of these in your logical test. Now, in this situation, we're going to be checking if the number enrolled is greater than or equal to two. So, we're going to be using a greater than or equal to. But, remember, we must compare two values. Whether those are cell references or values we type in, does not matter. Now, once we've done our logical test, the next second argument in brackets is the "value_if_true". And this could be a value we just type into the cell or it could be a calculated value. So, we could put an actual calculation here but the way the "IF function" will work is if the logical test equates to true, then whatever you've got here between the two commas will occur. If, however, the logical test equates to false, then it's going to do this third and last argument "value_if_false". And, again, that can be a value you have typed in or it could be an equation you've entered. Let's watch this work in practice. So, I'm going to now calculate if this student has siblings or not. I'm going to start with my "equals, IF and open my brackets". Now, my logical test. Here, is going to be if the number of students enrolled is greater than or equal to two. So, I'm going to click on the number enrolled, and then type greater than or equal to two, comparing two values with a logical operator. Now, I type my comma. If it is greater than or equal to two, I have a sibling, so I'd like a 'Y' for 'Yes' to appear in the column. Note, because I'm working with text, I put this in my double quotes or quotation marks. Now, I type a second comma. If there isn't a sibling, I don't want to clutter the column with a whole bunch of 'N's' for 'No'. I'd actually like to leave the column empty. If, I just type a space however, Excel is going to think I've forgotten to type my last argument and it will actually effectively ignore that space. So, that's not going to work. What I have to do is open my quotes and immediately close my quotes. That tells Excel to literally put nothing in the cell. Now, I close my bracket and press enter. As we can see the number of students enrolled is greater than one, we would expect siblings to be "Yes". Let's now copy that formula down by double clicking our fill handle. And you can see, it has put a 'Y' wherever there are two or more students enrolled. That is how the "IF function" works at its simplest. Let's look at a slightly more interesting example that actually uses the calculation as well. So this time, we're actually going to calculate a sibling discount. And we have been told that we're going to give parents a five percent discount on their calculated fee if they have two or more students enrolled. So, once again, we start with our equals, IF, and tab. This time, we've already calculated whether there is a sibling or not, so we can simply check our siblings column. So I'm going to check if the value in the siblings column is equal to 'Y'. Now, I haven't worried about the case. When comparing text, the equals is not case sensitive. But, again, don't forget your quotes. And now type a comma, this time if it is true, I actually want to do a calculation. So I'm going to click on my calculated fee and I'm going to multiply by five percent which should give me five percent of the calculated fee. Then, I'm going to type another comma. If the answer brings that they are not eligible for a sibling discount, then we just put zero. And note, I have not used my quotes because it's not text, it is a number. Close my brackets and then press enter. There's my sibling discount for the first parent. And if I, again, use my fill handle to copy that down with a double click, you can see it has now calculated the sibling discount for each parent ID. So, using the "IF function", we can actually make choices to do different calculations within a single cell. And what this allows us to do is introduce more power into our workbooks but also ensure we have consistent formulas going the whole way down even though those formulas may decide to perform different calculations. Keep watching though because all this keeps getting even more interesting. [SOUND]
