0:03

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]

Â