 Hello everyone and welcome back to another tutorial in this video We're going to be taking a look at the if function and also how we can nest the and and or functions Into or combining them with the if function So as you can see we've got some data or some demo sales data in B and C for so I've got a sales rep and the revenue that they have generated and we've got four other columns going from D through to G or Scenarios where we want to use our if statement to flag and identify The varying revenues based on our criteria So the first one we're going to look at is revenue that is greater than 10,000 So what we want to do by this is if the revenue is greater than 10,000 We want to return the value good into this column So we're going to have a varying just good or a blank cell here So in order to do this it's quite simple We just get start with our if function so it equals if open our brackets and the first part is going to be our logical test So all we need to do for this scenario is select our revenue that we've got in column C and Use our greater than symbol that we can get by holding down the shift and the full stop button and then type in the value 10,000 You could either reference a cell that contained the value of 10,000 or whatever value you want But for our example, we're just going to type in the number there and then we can do comma to move into value of true And all we're going to do in quotations. They write the word good and As a got end of correlations there as well and then for our value of false We're just going to do two question quotations Just so we get a blank result if it's if the value isn't not meeting our criteria We just wanted to be blank and then we can enter and then when we drag that formula down our page You can see that we've now got goods for all of those that meet our criteria So simply put if the value is greater than 10 You can see we've got the word good beside it And if the value is less than 10,000 you can see that it's got just a blank cell at the moment If you didn't want a blank cell and you want to put something else in there You could obviously put the word within these two quotations So maybe bad and then obviously that would populate that for you as well So we'll move on to our next scenario and as we go through these scenarios You'll see the the detail and the criteria will gradually increase as we move along So for this one we want to see We only want to flag good if the revenue is greater than 10,000 and less than 15,000 So this is where we're now going to introduce and our and function So let's just jump straight in so what equals if Open our brackets and in this time in the logical test We're going to be using the and function So all we need to do is type in and and then open another bracket and and is separated So we have a variant logical test I mean perform as many as we require but for us we're just going to have the two So the first part we're going to do is to check if the CFR revenue is greater than 10,000 And then we'll do a comma Whilst we're still within the and function to move on to the second logical test And the next part is to see again is if this is less than 15,000 So I can now close the brackets there and then we're now back into the if statement so you can see that before close the bracket this was With the pop-up prompt we have and then as soon as I go a close brackets We'll move back into the if and we're now ready to hit that comma to move into the value if true So get value of true is going to be good And if we don't doesn't meet our criteria Then we'll simply just put in the blank again and close our brackets and then we can close the function So our criteria is it's going to be greater than 10,000 and less than 15 So we only expect to see the word good against those values that are between 10 and 15,000 So if we pull that down We can now test to make sure that is correct. So we've got 14 5 3 2 So yet that's between our criteria 18 isn't 8 5 isn't 15,000 isn't in our criteria because we're only looking for less than 15,000 So we would need to put our equal symbol in if we wanted to do the 15 to include 15,000 So 14 500 works and in 12 works as well So just to touch on this 15,000 here if we wanted to include if it's less than or equal to 15,000 All we need to do in this second part of our and function is where we've got our less than there is just to enter an equal symbol And you can see it will now pick up the 15,000 because the criteria is now less than or equal to 15,000 so it's a real simple little update We need to make if we want to include the 15,000 else we could enter you can see it's not going to be included So for our third scenario in this time We can see okay, we're looking for if revenue is greater than 10,000 and less than 15,000 we want to do good So the same as what we previously had but this time we also want to say well if revenue is greater than or equal to 15 We want very good to be our word else display value for all others So if the revenue is between 10 and 15,000 We're going to display good if it's greater or equal than 15,000 will say very good and then display The value for so basically we're going to be displaying values for anything less than or equal to 10,000 in short So how do we do that? Well, we start off very much the same So we go equals if and then we're going to start off our and function as well So we want to first test to say is this greater than 10,000 and Also, is it less than 15,000? If that is the case, then our third of true statement is going to be to show the word good And then the value of false so this time we're not going to be just entering a value We're actually going to be nesting another if statement within our if statement to deal with our next scenario So this next part is going to be another if statement open brackets And you can see that our prompt is now moving into our next if so we can see where we are at this point We're looking at the logical test. So what we now need to do is we need to go, okay Well, if it's greater than or equal to 15,000 We now want to return the value very good Else so if value is false, we just want to display the value So all we need to do is just select the revenue so obviously if this criteria and these and Statements here and this last if statement here of fifth greater than 15,000 none of those been met Then all we're going to do is just display the value that we have in the revenue column So once we've happy and finished with that We still a closed bracket to end our second or nested if statement And then one final bracket to close our original if statement and we can see we now got the result of very good So we can just now go back and we can see that formula in its entirety So the first part is very much the same or is exactly the same as our scenario ahead in column E So we've got if our two and statements of if C7 our revenue is greater than 10,000 and Correct revenue is less than 15,000. So if that scenario what's play good? if however, none of those are met and our Revenue is greater than or equal to 15,000. We want this play very good. And then lastly display the value So let's just pull that down and we can go for each one and address it accordingly So the first one is showing very good and that's because our revenue is greater than 15,000 Second one seven five hundred yet. So that's a less than 10,000 so it's falling outside of our original criteria We've got a good here because it's between 10 and 15 Nine because it's less than 10. Yes 10 10 and then we can see we're now got our greater than or equal to 15 So that's now obviously falling into a very good category Again less than 10 above 15 between 10 and 15. Yes and lower than 10 So you can see that that now has worked and that's obviously given us the scenario you want and obviously this is the results that you Can see from that as well The last one we have is if our revenue is greater than or equal to 15,000 or 15,000 Or it's less than or equal to 10,000 So all we're really doing here is flagging those values that obviously Are going to be above 15 lower than 10 So you're all we're really doing is blanking out those between 10 and 15 So a bit of a reverse to what we've been doing already, but this is a great scenario to get at least let us use the all Function so it's gonna do equals if open our brackets and this time we're going to be using the all function So we'll open our brackets and this works exactly the same as and it's just that it's gonna rather than Requiring both values to be correct It's only saying that one of these logical expressions has to be correct to return the true statement So the first thing we do is select our revenue and we want to say okay So if that is greater than equal to 15,000 or It is again selecting it again before doing this is less than equal to 10,000 And then close our brackets. So if one of those is true We want to obviously return the true statement if function. So if that's the case, we want to display the word warning Close brackets else just do a blank and this is how our function looks So you can see we've got all statements. So one of these two logical statements need to be true For the warning where to be displayed else if neither are true then we're just going to get a blank cell So let's do that and pull that formula down Cool. So we can see that everything that should be what so basically the only ones that should be blank are those between 10 and 15 So we've got a 12 here. We've got a 14 5 and 14 5 yet. So we're basically getting a warning for everything else So that's just another example a example. Sorry to show you how the or function is working And let's just quickly go back through those so we can see them one more time So revenue is greater than 10,000. So this is quite a simple straightforward function or formula All we needed to do is select our revenue and identify. It's greater than 10,000 Display a single value of good if obviously it's correct and blank if not So greater than 10,000 less than so we just used our we just extended our original Criteria what using D to include our and function So in our logical test we used and and we needed to make sure that both of these logical statements within the other Function where we're met for us to return a true statement from our logical expression in if to then get our good text returned For the third one we used obviously the and function So exactly the same as we did in column E up until the word good And then after that we nested a second if statement To then do a comparison of greater than equal to 15,000 to see if we've got a very good rating Else we just returned the value in C7 and made sure we closed those brackets off twice So we could close off the original if or no the nested if and then lastly the original if statement And the last one we looked at is the or so all we did is nested all within our logical expression of the if statement To ensure that one of these logical expressions were met for us to return a true statement within our if formula So we could return a warning text if we if the logical tests were met Else we displayed a blank So we hope that this wasn't skimmed too quickly I hope I was in detail but given it keeping it nice and concise if you do have or have any questions at all Please drop a comment below this video Alternatively, there are links to our Facebook Instagram and our website in the description for this video There's also some other with links in that description as well So why not check them out? But most importantly if you do any questions you can get in contact with me there as well Lastly, if you haven't already, please do make sure you subscribe to the channel hit that Bell notification button So you're notified of all of our future videos and Well even more lastly, so if you did enjoy this video, don't forget to give the video a like It's not only greatly appreciated by myself and let's you know what content you'd like to see more of But also it's going to really help the channel in terms of the YouTube algorithm Thank you very much for watching and we'll see you in the next video Before you go don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlists so you can see these categorized together So make sure you check those out and get all those useful information And obviously as always don't forget to subscribe and hit that Bell notification button