we're going to do some spreadsheets in microsoft excel i'm going to look at nested ifs before we get into initiatives let's just recap what an if is okay so in excel we have an if statement and the way it works is then you use an if statement if you have two different things that you want to display or calculate so what we have is you have an if command function you open bracket and takes in three parameters the first parameter is the condition it's a question that you ask and that question must be able to be you must be able to look at it and say yes that is true or no it is false so you can't just say greater than 40 because like what is greater than 40 you say like a cell is greater than 40 or a cell is equal to something or a value is it is not equal something so you're normally using your not equal to and your your equal to greater than less than symbols in your condition and you're comparing two things to say this is definitely true or it's definitely false then your second condition is going to be what will happen if the condition is true if it's true then you must only do this part of the if statement in other words this could be a piece of text that is going to be displayed if it's true or it could be a calculation that gets done if this is true so it's up to you then this third parameter after the if it's true is what must be done in the event the condition is false so this will only be done if the condition is false so if the condition condition's true it'll jump to this part and then it'll only do that part and if the condition is false it'll jump to this part and only do that part and that will be again text that must be displayed or a calculation that must be done in the event that it's false so now that we know what an if statement is and we know that we use it to display two different things what about this scenario here we've got a whole bunch of people and they've got codes and i want to write down what their type is based on their code now if they're a p they're a part time if they're e they're employee and if they're a they're admin see this one's a little bit more tricky we can't use an if statement here because there are three options so that makes it a little bit more tricky so what we're going to do is we're going to use nested ifs so let's pretend one of them doesn't exist i'm going to scrap the admin let's just deal with these two for now or just one for now actually just deal with one so we're going to use our if statement like we know so we're going to use an if statement over here we're just going to work out the add or the the title the type for bruce so we're just looking at bruce for this example and when we copy down and switch for all the other people so let's look at bruce equals if open bracket well we're going to check how do we know if they're part-time employing ever we're looking at this code over here so if this cell b7 that b7 value is equal to a p which you put in double quotes because if it's equal to the text p what do we want to display here we want to display the word part time so i'm gonna in double quotes say the word part time okay you could refer to this actual cell h1 if you wanted to but i just want you to see visually what's actually happening here so if b7 is a p then display pattern that's easy but if it's not part time here's the problem we've only got one thing left and there are two possibilities two possible what do we do with this two we use it if so what i'm going to do here in the else this this false part i'm going to put a brand new if statement inside here in the else in the false part for just these two scenarios so forget about if we get to this part over here if we get to the false part here we know for a fact it's not part-time so therefore it can only be employee or admin so over here we're going to put an if statement a brand new one this is the nested if the if inside the f so i'll call this the inner if so now we're going to look at that code again if that code b7 is equal to an e then what's the true value it must be an employee employee and if it's false well if it's if we get to this false this is the inner if we know that it's not part-time because it wouldn't have gone into this part if it was part-time we know that it's not employee because it's going to the false part here so the only other possibility would be that it's an admin so then and here's the tricky part what you you close the brackets for the inner if and you need another brackets for the outer if so that remember there's two brackets one for the inside if one for the outside if so if b7 is a p display part time if it's not then shift to this if statement which says if it's an easy employee and if it's not part-time and it's not employee then the only other possibility is that it's admin and if i copy that down it should work for all the options that's a way of displaying three possibilities easy okay let's do another example but in this case we're dealing with numbers okay so we want to say okay these guys have sold this many sales and depending on how much they sold depends on their level okay so yeah we've got anything 250 or less is bronze uh between 250 and 600 we're going to include them with silver and then 601 is gold okay when you're dealing with numbers this is my big tip ignore the middle one don't look at the middle one yet it's easier if we just look at the top or the bottom one okay i don't know if you that's the top one or that'll lot depends how you look at this okay so ignore the middle criteria because to try to determine if the values between those two numbers is a little bit more complicated it is possible but we need to learn other functions for that for now with what we know i just want to look at that option or that so you tell me which one do you want to use i think let's let's start with that let's start with bronze so again there are three things to display so we are using the nested if so equals f open bracket what's our test we want to check for bronze we know that if they get 250 or less then they get bronze so if the sales d7 is less than equal to 250 what's the result it's going to be the word bronze i'm going to type it in now you can refer to the actual cell but i'm just going to say the word brown so we can see it so you could actually refer to k1 as the result for that okay so if it's less than 250 including 250 say bronze now if it's not bronze then it must be one of these two options so we're now going to use an if statement here to cover the two options by itself but remember my tip ignore the middle one so if we've done bronze and we're ignoring the middle one that means we must do the gold one let's look at the gold how do i know if it's gold if it's greater than 600 if the sales is greater than 600 then it's gold not including six hundred because it's six hundred so if the sales is greater than 600 then it is gold you type in the word gold okay and because there are only three options if it's not bronze and it's not gold the only other possibility is that it's silver and this way we avoided having to put some sort of criteria or condition for for that for between 251 we've got it easily because it's what's left over so remember what do we do we close the inner bracket close the outer bracket so if it's less than 250 it's bronze if it's false then if it's less than 250 that means it must be bigger than 250 so it's somewhere in this range so therefore we check if it's greater than 600 it's gold if it's not greater than 600 then it must be solved because although it's less than 600 we only hear if it's greater than 250 so it must be in that range and that is how you do a nested if so if you are wanting to display three possibilities they'll normally give it to you something like this in a table then you know you are using a nested if you first check your if you deal with numbers don't do the middle one pick the top one or the bottom one check it and do what is needed for the first option and then in your false part you do an if statement which covers the bottom to the next two options but remember avoid the middle one for your condition leave that as the the else the false part okay that is a nested if for other videos in the series on excel go to our youtube channel follow us on facebook like follow us on twitter subscribe we'd love to hear from you and remember don't do it the long way do it the mr longwear