Transcripts
1. Intro: Hello and welcome to this Skillshare course. The purpose of this course is that when you chew all of the various IF statements that exists within Microsoft Excel. This course, we'll look at some of the following techniques. First of all, I will look at an if statement. Then I'm gonna show you how to nest AND, and OR, or formula within an if statement. I want to look at COUNTIF and COUNTIFS, SUMIF and SUMIFS, averages and average Fs, max if DFS and BFS. And one look at how you can use an IF error former or an FNA formula. And I finally on look at a new IF statement that is included in Microsoft Excel called the if statement. By the end of this course, you should be totally proficient in these various Excel functions. This will allow you to carry out various Excel task quicker and reduce the possibility of errors.
2. 1 If Statement : Yes, Damon tests specify condition and we're drawn to one value for Atreus old, and another value for a false result. There are three parameters which we must populate in an if statement. The first is the logical tests, and this represents the question you want answered by the, by the if statement. The second is the value of true, and this is the value you want returned. Showed you a logical test that you specified in the fourth parameter, be correct. And then lastly is the value of falls, and this is the value you want returned should the question you asked in your logical test be returned as fault. So let's look at an example. Here in my Data tab. I have a series of trades and sales. And I want to populate a fly here that tells me if the trade was international or domestic. And how do I know if it's international or domestic is if the trade happened in Dublin, it was a domestic trade, and if it happened anywhere else, it was an international trade. So I'll open up my if statement. And the first parameter here is the logical test, which again, just to go back is what the question we want answered. So what I want to know, what is the city Dublin? So ISO is D4 equal to Dublin, and I'm specifying text here. So we need to open up my quotations. And you do sit, type in Dublin, close my quotation and then the value of true. So if it is equal to Dublin, I wanted to return domestic to inform me that it's a domestic trade. So again, I'm going to get drawn a text value. So one, open up my quotation. They've been domestic. And close quotation. My toy parameters, the value of fault, and if it's not equal, DO blends of the trade happened anywhere else ordered in Dublin, we know it's international. So I want to specify text by open up the quotations and type International. And I can close that here. It'll automatically tell me that this is an international trade, is the trade-off plays in Paris. So it is International can drag that down quickly and straight away I have a flag for every trade and foreign me, whether it is domestic or international.
3. 2 IF (and) Statement: One of the limitations that you steam it has is that it only allows you to specify one criteria in your logical test. We net round is by nesting and, and formula within your if statement. So within your logical test, you'll use an AND formula. The AND function is logical function used to acquire more than one condition at the same time. So here the n formula will allow us to specify multiple criteria for our if statement. Because we nest the n formula in the logical test, are far off the if statement. So the fields to be entered in an n formula called logical 1. That's the fourth condition to be evaluated logical to the second edition logical tree, logical 4 and so forth. So let us look at an example here in the data. So I want to return, I want to know if these are domestic and in-store. And so if the domestic in store I want on yes. And if and I want to return no. So by domestic Do you need to be in Dublin and in-store? The needs will be in specified as an inter approaches type in column F. So let's look at how we use an if statement to return this value. So I'm going open up my if statement, an enemy into my logical test. And now as part of my logic test, I'm going to straight away open up my statement or my end formula. The first parameter I want specified is city. So I wanted to know is the city in column D. So D4 equal to Dublin. Don't forget to put your quotations. And then hematological two. And I want to know, is the porches type is O is F4 equal to in-store? Again, it's early for your quotations in and I'm going to close that. So those are two criteria. So a logical OR logical to us, I'm going to close my formula now. And as I close my formula, it brings me back into the fourth parameter of my if statement. And that is now Dawn sort of forced the logical test is my An formula. And long today in row to go into my value of trues. And a value of true is, if it is true, I want just return the text. Yes. And if it's not, I just want to return detects and they'll see here are the sale took place in Paris and top place online. So it's not a domestic in-store, a trade. It will go down here. We can see this one sale in row 11. It was a sale in Dublin and it happened in store, so it meets the criteria specified in our if statement, both criteria and it will turn, yes. So the if statement is a very useful tool when you want to assess more than one criteria.
4. 3 IF (or) Statement: When an if statement May want to specify true value, should logical test meet one of multiple criteria. How we do this is by using an OR formula nested within our if statement. Your function is a logical function to test multiple conditions at the same time. And it returns either a true or false value. Similar to the formula you have, the fields we entered are called logical one logical to, and these represent the the conditions or criteria to be met or evaluate as part of the formula. The formula will return a true if it meets one of the, one of the criteria specified in the formula. So let us look at an example. Here in column L, I have a field called stationary. And what I want to know is, is the product categorize as stationary. So if I look at my product name, what I have here is Gs knives, Margo, Penn, plasters, and printer. It's a marker or a pen. I wanted to be categorized as stationary. So how I do that is I open up a formula and then I'm going to my logical test. Here. I will show it into my OR formula and logical 1. First of all, I want to say is G4 equal to Penn? And I want to put quotations around pen. And then my second logical for is my second logical to MI or formula is, is G4 equal to marker? So text marker. So this means, is it if G4 is either equal to pen or a marker, air or formula come back as true. So I'm going to close my formula there. This brings me back into my if statement. My logical test is complete and my value of true is yes. And my value of false is simply no. And then I can close my if statement. And you can see here that it's not a this one comes back no, because placers isn't categorized as stationary. And I can bring it down here. And items that I have a marker or unknowns that are pen, comeback as yes. So that's useful where you want to specify multiple criteria. And it comes back as yes, based on only in one of the multiple criteria you have specified.
5. 4 Countif: So my formula allows us to specify a specific criteria in order to some range of values. There are three parameters to be populated in us on earth. The first is the range, and this represents the range of sales where you want to apply. The criteria against the bacteria is where you specify the condition that you want, apply it to your sum. And sum range represents the range of values that you want. Add it up. First look at an example here. Here I have a number salespeople and I want to know the sales they've made. Where that data is in this Data tab. And have the sales here in column C. And in column I, thirst quantity which is made of units sold, and I want that some dope per salesperson. So I opened up my sum F. The first parameter is my range, and this is the, this is wash. I want to apply the criteria to. So that is all the data here in column C, which includes the salespeople. And because I want to write down later, I wanted to select all of the, all of column C here. My second parameter here, criteria is, the second parameter here is criteria is what I want specified here against that. And what I want specify here is the name and the name here specified column I. So I'm actually going to go ahead and select the value in column I. So this is telling us, so myth that only some the quantity if it is Sinead with Patrick and the sum range. Well, that's what I want out. It opened in this installment sales, which is represents the quantity here and call you my so I'm going to select all the data here and call you Molly. And instantly that will give me return a value. And so all of the sales quantity per salesperson.
6. 5 Countifs: When we want multiple criteria specified as part of so myth, we use another formula called the sum Fs. The SUMIFS formula will allow us to specify as many criteria and as many criteria ranges as we want it to be summed up, sort of feels to be entered. We start with the song. The first criteria is to sum range, which is the range of values that you want. So and then you specify a range one, which is the first range of data that you want to condition applied to a criteria. One represents that condition, but you want to play two range one. And then we can go into range 2, which is the second range we want to evaluate and criteria to is the condition that we want apply to range 2 and so forth. And we can have as many criteria as we want. So if we look an example here, I have a series of salespeople here, and I want an order online sales. So say I want to know. So I've two criteria. One is the salespeople, one is the porches types of I want the I want it to ASAM based on if the salesperson matches and based on if it was online or in store. So if it was our online only. So I've two criteria it I need to apply. So let's look at how we would have we would quickly some of the online sales for a salesperson. The opener parcel knaves, making sure to click SUMIFS rather than some. If the sum range is the online sales, Oh, this is the quantity of units sold. So that's the data here and call them either are selected and forced. The criteria range is the first range of cells we want evaluate, and that is going to be column C here, which is the salesperson to criteria is the name of the salesperson. So I'm going to select my data here in column I next to where my formula will be. And that's going to be the name. And the criteria range too. Well, is the data here in column G, r, sorry, in column F is porches type. So unless like the data here in column F and the criteria is online, so I'm gonna type in online. And Diane, I can close my SUMIFS formula. And this will specify the online sales, fortunately for us Patrick. And then I can instantly drag that down. And quickly I have a quite have the sales data only for online and this deep. So this will exclude any of the in-store sales. So this only is very useful when you want to apply multiple criteria to your so myth.
7. 6 Sumif: Again, IF formula cancer cells that match criteria, there's two fields to be entered. First of all is the range, which is the range of cells that we want counted. And criteria is where we specify the condition of dash will be applied to the CAPM formula. So look an example here. We have a list of cities and we know how many orders took place in each city. In my underlying data here, each entry represents an order or transaction. So I can get a count of the orders by just specifying how many entries apply it to each city. So what that is is I can open up a candidate if the range will be all the data here in column D. And my criteria is the city that I want candidates. So that is I can set the value in I2. And then I can close this and I can see how many orders took place in Paris. And write that down. And straight away, I have the number of orders in each city.
8. 7 Sumifs : The Canvas formula allows us to count the number of cells in a range that match multiple criteria that we specify. The fields we entered firstly, is range one, which is the fourth range to evaluate criteria one, which is the condition that you want to apply to arrange one. Then Range 2 is the second range you want to evaluate an EKG criteria to is the condition that you want to apply to range 2 and so forth. Which range? Tree range for et cetera. So here I have number of the city specified here, and I want to know the installer orders. So I want to do a count of the data here, where the city, where I expect based on the city and based on whether the portraits, typos online or in store. So I have two criteria I want specified. So how I do that, I can use my canvas so I open up my COUNTIFS formula. Criteria range one is the city. So that's all the data here in column D. Criteria one represents the city specified in the table here. So I just, I select I2. And Emma criteria to is why I want to know what is it in store or online. So I'm only counting in-store orders this time. So I'm selecting my range criteria range to is the data here in column F. And a criterion does instance will be in store some specifying a text value. So I open up quotations and I type in store. And then I close my formula. And I can drag this down. And I have all the in-store orders for each of these cities.
9. 8 Averageif: Language if returns the average of all the cells and arrange that meet our criteria. The range is, the range of cells that we want. Our criteria applied to. The criteria is what we want specified in order to in order to apply to our average IF formula. And the average range is the actual range of cells that we want the average function to be applied to. So if we look at an example, I want the average margin for each city. And so this is based on the data here. So first of all, I want to thank my city and what I want I, which is this margin data here and call it m k. So I open up my average if the range is the fourth criteria want blade, and that's the CV. So I want to select the data here in column D. The criteria is actual city specified in the table. So I want to select powers here in cell i2. And then I want, well, I want to tone is the average margin per city. So my average range is definitely merging data here in column K. And this will return an average margin for each city.
10. 9 Averageifs: The averages formula allows us to calculate the arithmetic mean of all the cells in a range that mean Warner more criteria critically, the averages formula allows us to apply multiple criteria. We look at the fields to be entered. We have the average range, which is the range of cells that we want. The averaged range one represents the fourth range of cells we want evaluate it. And criteria one is the condition that we want apply to that first range. Criteria to range to it is the second range one evaluate and criteria to is the condition we want to play it arrange two and so forth. Look at an example. We want the sales, we have the list of sale people here, and we want to know that our average online sales. So what we want to know is what sales each person may, but only for online. So we can open up our average if there are average, if's rather our average range is the number of sales, which is this quantity data here. Our criteria range one is the data in column C which has been to salespeople. And criteria one is the salesperson specified in the table. So we go back to the averages tab. We select cell I2. And then our second criteria range, well, is the purchase type. So in the Data tab we have portraits type in column F. So we're going to select all data in column F. And the criteria here is online, so I'm going to specify text online. And it will give me an average online sales figure. And then I can drag that straight.
11. 10 Maxifs and Minifs: The x L max function returns the largest numerical value that meet one or more criteria in a range of values. In contrast, the Excel minutes function returns the smallest numeric, numeric value. That means one or more material in a range of cells. The criteria to be applied is, first of all, is either the max range or the Min range dependent on which formula using. And it will be the range of values used to determine the maximum or the minimum range. One is the forest ranger value and criteria one is the condition you want apply to that range and so forth. So let's look an example here in this box I want to turn firstly, large sale that he says personal mesh made, and secondly, the smallest ale that each person met. So I'm going to start off by opening my max is formula. My max range is the range of cells I want to determine. So this instance, it's the largest sales. So in this instance I'm going to be selecting the data here and call him I. My first criteria is the salesperson. And that's the first criteria range and the second range. And the second parameter is the actual criteria itself, which is the name of the sales person specified in the actual table. So if I go back here, I can select that sector salesperson and I can stack more criteria if I wanted to. But in this instance I'm not going to. And that'll tell me each neighborhood Patrick's largest sale. And I can drag that down and easily at the largest sale made by each salesperson. Likewise, in order to get the smallest sale, I can simply use the formula. The mean range again will be the same here. So it's the quantity data here in the data. In column I. The criteria range one is the data in column C, and criteria one is the name specified in the question in the table. And I can close that. And then I have the smallest sale for each person.
12. 11 Iferror : These LFSR function returns a specified result when a formula generates an error and a standard result when no errors detected. So there's two fields which need to be put into if our formula is value, which is the value reference or formula which you are checking for an hour. And then the value of error is showed, showed the value or the formula you specified in your first parameter showed that were torn as an error. If this YL, instead of returning an error message, it'll return the second parameter or devalue if MRF that you specified. So let's look at an example here, a couple of sales here, and then of the revenue generated and the sales figure. So in order to get the average, I want to divide the revenue by the online sales. And Friday to 0, I will obviously get a hashtag divided by 0 error. But I can get round-off error message by saying equals IF error. And I get my value function. So here I'm going to do the actual calculation that I wanted to do, which is revenue divide by 0. No value IF error. And the heroes Act say here I'm going to specify a value I Robert warned, instead of that divided by 0 error message. So in this instance I'm going to say no sales. And then I can close my formula and see here, draw it out then. And then. So finally ones that it can perform the calculation, it'll return the result of the formula. Both should wear. The online sales is 0 here, and it'll return an error message. It'll return no sales instead of the error error formula.
13. 12 IFNA : The Excel if NA function returns a costume result when formula generate the hashtag N A error and the standard result when no error is detected, the hashtag, any error typically comes when a formula or a lock-up can not be found or cannot be identified. The if now formula is a work-around where we can get, specify an exact result to be returned should we get that specific error message? So the error message to how to get an a not available error message typically looks like this. So here are the VLookup and this name is Brian Byron. Name cannot be found here in this data here. But instead of returning the error message, I want to return a specific value. So how I can do this is I can open up an if now formula. Here. My value here will be the actual formula that I want to assess. So in this instance it's a VLookup that was already there. So I'm going to move past the ash, an element, the value effect if NA and desk to devalue our tone showed my VLookup be on successful in finding the lookup value. So I'm going to stay not found and I specify a text value. So I'm going to put in quotations and say not found. And then I can close my formula. And as I drag that formula down, instead of for toning the hashtag N A error message, it will just return the not found parameter that I've specified.
14. 13 IFS Statement : In 2019, the Excel dysfunction became available. Dysfunction ones multiple tests and returns the value corresponding to the first true result. The fields to be entered are firstly Test1, which is the first logical test, and then value one, which is the result when the test one is true. And then test to which the second logical test and value 2, which is the resultant has two, is true and so forth. So let's look at how we can use this formula. So here we want to specify a country and we have three different cities with TBI, Dublin and Paris. And we want basically return a country based on the actual city. So I can use an S former and logical test. One is going to be, is D2 equal to Paris? And after Paris in quotations because it's taxed. And if Paris, if D2 is equal to Paris, I want to turn France. I want to try and text value France. A logical test two is, is if D2 is equal to Dublin, then my value if true, is going to be the text value Ireland. And logical test tree is if, if D2 is equal to divide, then the value if true is UE. And then I can drag that down. And it'll instantly you return the country of each city based on what I specified in my if statement here. So that's quite useful formula, but bear in mind if you're working off an older version of Excel, it may not be available to you.
15. Outro: Congratulations, you've just completed discourse on the formula in Excel. Thanks million for completing this course. And I have some other videos and courses if you'd like to check them Irish, and also if you like, leave a review, that would be much appreciated. Also, tanks and congrats again.
MS Excel Skills, Microsoft Excel Skills by Michael Smith
