Transcripts
1. Introduction: Hi, and welcome to the class of the 15 most useful formulas in Excel for business. In this class, I'll be teaching you to 15 most useful formats that I've compiled through my experience as a freelancer. My experience includes working for different large organizations where I helped to set up big Excel sheets and also thought people how they can use Excel. I've done this, accompanies live ING Bank and an investment partners sleeker and some other gates that I have done. Besides I've completed my master and digital business and innovation. Or the majority of my classes were related to business intelligence and data analytics. So in this class, we're going to look at the 50 most useful formulas. Whether you're a beginner in Excel or you're just a marketing, finance, or logistic professional-looking to brush up your Excel skills. This class is great for you. We're going to go over the basics of Excel. So really the basic form of.'s, you need to know the fundamentals. But these can also help you to become more advanced once you start combining them. So the class is built up in 15 videos. In each of those videos, we're going to discuss one function or one group of functions that you can do an Excel. Within those videos, we're going to cover three parts. I'm explaining to you why the formulas useful. I'm also giving you an example of how it's being used in practice. And third, I'm going to show you how you can make the formula yourself. And these can be formulas from VLookup all the way to the summit, but also functions like Mao and the today function. Excel can be an incredibly complex software. There's endless possibilities. And knowing these 15 basic formulas and we're going to teach you in this class, is really going to open up opportunities for you to really start building cool things in Excel. So I hope I'll see you in the next video where we'll be discussing the outline of this class. So we're going to see that which other 15 formulas, group of formulas that we're going to see in this class. And I'm going to walk you through it, a project that will be doing along those classes. So I hope I'll see you there.
2. Class Outline: Hello and welcome to the class overview and project description of the glass of the 15 most useful formulas in Excel. In this video, we're going to discuss how the classes are buildup and watch. Each video contains and how he explain everything within those videos. And we're going to discuss with you the assignments that are included in this class. So here are the 15 formulas, the list of formulas that we are going to discuss in this class. In each video, we're discussing one formula or group of formulas. And after each video, we will be doing an assignment. So within each video, when we were discussing a formula or a function, we're first going to look over what this formula should be do. So I'm going to explain to you what the purposes of using the function of the formula that I'm going to tell you a quick example of why it might be handy to use this 1. Third, we're going to be a blind for nine. This is the biggest part of each video where I'm going to show you how you have to do the formula and how what you have to pay attention to in order to do formula properly. Then we have in-between each video, we're going to have to do assignments and we're going to do it that way every video. So after each video, I'm going to ask you to do the assignments that are connected to video. So first, obviously watched the video and then go to the Excel file. And then under the tap or sheet assignments, you can find the assignments and it's divided br, br lesson. I'll show it to you in a bit. And third, you can check your answers under the sheet, answers for it if you get stuck, I recommend you to re-watch the video. Also go into the answer sheet and look into the formula bar what has been done exactly. But if it doesn't make sense to you yet, then I just recommend you to re-watch the video to familiarize yourself a little bit more. Okay, so this is the Excel file. I have a touch attached to this class. And as you can see, we have the 15 functions and formulas right here. So the idea is that in the video I'm gonna show you what the formula does within those little sections. After you've seen the video. And the assignment is pretty basic. Recreate what I have done in the video just to familiarize yourself with the formula and the function. I'm just doing it once really helps you to quickly grasp what the formula does and how to do it, helps you remember it more easily. Then if you have done the assignments and you can go to answers and see the answers, you should get out of the formulas that you perform. If you see a difference, I recommend you to compare in the formula bar right here, what is different in the answers then would the assignments. And if you still can't figure out, rewatch the video in the data sheet. This is the data that we are using 40 assignments. So most of the sections, most of the assignments will need data and it's the datasheet we'll be using. So that's it for the project overview. In the class overview. I will hopefully see you in the next video where we are going to discuss the first formula which is today and the NOW function, two very similar functions, but with a slight difference. And I'm going to explain to you why this one can be very useful. I hope I'll see you in the next video.
3. NOW & TODAY: Alright, and welcome to the first formula that we are going to discuss in this class. Today's formula, it's going to be to today. And the NOW function. We will start off by explaining you what those formulas do. And then second, I'm going to show you a few examples of how you can use them and why they are very useful. Okay, So today and now function are almost the same. The only difference is that the NOW function returns time as well. Whereas the today function, it will give you the date of today. There are very simple. You just type in now. You open it, you close it, and it will return to you the 19th of August 20201744. So now if we enter the today function, it also gives you 19th of August 2020, but it won't give you to time. Now a quick side note, it doesn't automatically update, so it doesn't automatically recalculate, which means that even if in a few minutes we would leave this spreadsheet, it's not going to give you the current time in a few minutes anymore. It will be delayed and will freeze on 1744. Excel updates when you tried to calculate something else. So you'll see that in the next formulas that we are going to make, the 1744 only updates when I'm not our formulas being calculated as well. In case if you're looking for something that updates every second or any minute, I'd recommend you to look into Google Sheets. Alright, so those are the two functions. It quickly gives you today's date or today's time. In. This feature can be very useful for, for example, when you want to calculate the sales of the last seven days shown in a previous video in this class, we're going to use seals, data seals entries to show our formulas and show the features that I'm discussing. So we're going to calculate the last seven days sales, including today. It's an important side note, I'll show you why. For this formula. Before we start, I'm going to use some more advanced features. Functions like sum if. In case you're not too familiar with sum, if don't worry, door if you don't understand it right away, how to sum if feature works is something we're going to discuss in the next lesson. But I just want to show you what's possible using today or to now feature. Alright, so we are going to calculate the sales of the last seven days, including today. What we will do, we will start off with writing some. If we open parenthesis. And we first put in the criteria range. So what we are doing right now is we're going to look for a range. We're going to give it a criteria. And if that rage Manchester criteria, we're going to sum another range. So in this case, if the order date is within the seven days that we're looking for, it is it is within this seven-day window. It's going to check if that's true. And if it's true in the same row, it's going to take out revenue and sum it up. So that means that our first range we're going to select in this formula is to criteria range, which is the Order Date. Next up we're going to write is sorry, it's bigger than or equal to. We're going to add the n feature right? Today. We're going to open it and close it like we did in the EU had our cell minus six. So, so far what we have is it's going to check if the data that we see in column H is sorry, the order date is today or within the last six days. Six days. And if that's true, we're going to sum the revenue. So there we go. We have a revenue in the last seven days of $136 million. And it's very simple. If we want to do this for 30 days, we just copy and paste it and we changed this six into 29. We do we don't take 730 in the formula because we include today. So six days ago is today is Wednesday. So six days ago was last week, Thursday. So all those days together from Thursday today, Wednesday is seven days. And therefore we don't include video, make it seven or remote, but don't make it 30 into formula because otherwise we're going to calculate from last Wednesday to today's Wednesday, and that would be Eight Days a Week. Alright, so this is the revenue of the last 30 days and enforced today. It's almost the same, but we don't want to know if it's bigger than a certain date and all we want it to be today. So the Order Date needs to be today. So we're going to take out these bigger dense symbol, and we're going to take out this minus 29 and what it says now instead, if the order date is today, don't read R1 to calculate and sum up everything that's in column P. So there we go. This is the two-day feature. I hope this was useful lesson. I personally use this feature dysfunction a lot in Excel, and I hope it works for you too. So in the next video, we're going to look into some functions. Functions in plural because we're going to look at the different variations of some under which for example, Summit and a normal sum function. Alright, I'll see you in the next video.
4. SUM(IFS): Hi, and welcome to the second function that we are going to discuss in this class. And this class we are going to discuss this some function. Some function is a pretty easy function. And if you select a range or select a couple of cells with this function, L just accumulates summit and return true to total. To sum. Function is one of the most used functions out there and excel. And that's also a very simple one. I'm going to show you this with the total sales. I'm going to write some, I'm going to open it. I select column P, which is an infinite column, because it doesn't stop at a certain row. We're just going to write p double dot py. And although we have fixed in the first row, it will still sum all the dollars that we have, and it gives us the total. So what happened now is we selected a range. In this case, this is P0 2pi, which means to complete column, no matter how many rows sums everything and gives you the total BEC. Very simple one. You can also select a couple of cells or select a wider range over multiple columns. It will give you back the total. Then the first variation of this feature is to some feature. So in this scenario we are going to sum, again. We're going to select our range. We're going to sum under the condition that in the same row it says Middle East and North Africa. So we will open up some, if every row is another seals deal that has been made. And what we wanna do is sum, their revenue if the seal has been made in the Middle East and North Africa. So in this case, some if starts with the criteria range, which is column a. This is where it will give us whether is Middle East and North Africa. And then we're going to add that criteria. So right here, you can follow along with what we are writing down. So reopen up with this sign. And we're gonna make it a formula, which means that we close it again. We put the end symbol. We go back to a Formulas tab and we select here Middle East and North Africa. And then in the third step, the third part is over. You're going to select the range that we want to sum. In this case it's p. So in this case is going to check it a, it says Middle East and North Africa, which we refer to by clicking on cell E3. And if that's so in the same row, it takes to revenue and accumulates it. Sorry, there we go. This is all the sales that has been made and the Middle East and North Africa. Now we will go through to third variation, which is some if's. This means that we're going to have multiple criteria. It's very similar to the previous one. The idea is similar, but the buildup is different. In case we start with a criteria range, we're going to start with the sum range, sort of rage. We eventually want to sum up, which didn't change. This is still the p, the revenue where we put it at the end of the formula. In some, if we're gonna put it first in some if's reason for this is because now, now we know which column to sum up, we cannot put an endless amount of criteria into this formula in order to give back to Total based on that criteria. So in this case, in We said that everything that's in Europe and seals channel online, we're going to sum. So we start off with the first criteria range reaches column a again. And then we are going to refer to Europe. But as you can see, this doesn't just say Europe. So instead of referring to the cell, we are going to ride down here and we're going to close it. And then in next step we are going to add another criteria, arrange a new one, which is going to be d, because that's right, it gives us the sales channel. And we did a same thing with Europe. We're gonna write is, but then online, we're going to close it. And now we have two criteria. So we're going to sum column P, which is the revenue. If a matches Europe and call him deep Mencius online. And here it is four million, nine hundred thousand, nine hundred and fifteen thousand. So these are the three variations of some, some, some if and some. In the next video, we are going to do a count functions. Conferences are very similar to the Sun feature. We're again going to look into count, COUNTIF and COUNTIFS. I'll see you there.
5. COUNT(IFS): In the third video, we are going to discuss the function count. It's pretty similar with what you can do it then some, but as the name already suggests, count, counts the number of cells and some, some amount of cells. So we're going to start off by showing you two different types that you can use. The first one is count. You open a parenthesis, you select a range, and you close it and now accounts to 0. And the reason for this is because count only works for cells that contain a number. So if we were to change this to, let's say n units sold, which contains numbers. You'll see that it now gives us 28,556. Now there is another type of count, count a, where you do include all the cells that also contain text. The only thing that doesn't count is the cells that are empty. So we're gonna open up. We are going to select a. We close it and we get 28,567. The reason why this one is one higher than this one is because this one didn't include the header, which is a text. And this one does. Alright, so now we know that we want to know the amount of deals that were made. Obviously this one is provide number because the header in our data sheet is not a deal. But every row for the rest ace, every row that's not empty. So we're gonna take away this one. So the total deals remade as 28,556. Now, a variation of the count function is count. If, like we did in the last lesson, we now want to know how many deals were made in the Middle East and North Africa. To do this, we are going to select a wrench, which is a, How are we going to create our criteria? The criteria is that it must equal what's in here. We don't need to add, like in the last lesson, we don't need to add a range to some because it will just count the amount of ROS. And therefore, this formula is done. And we can see that the total number of deals and Middle East and North Africa, the total number of entries in our data sheet is 3,456. Questions quickly to a number because it's not an accounting. It's not a value. I mean, it's not a currency. And now we want to know based on criteria, criteria how many deals were made. And these criteria are Europe. And that it's 3D online channel. So instead of count, if we're going to write down count, if's, we're gonna do the same steps as we did in the last formula we might, which is we're going to select our criteria range. We're going to make our criteria, which is Europe. We're going to write it down now because I kinda referred to as cell because it says Europe n on line. And then we're going to create this second criteria, which is in column D. This must be online. So now it's going to count every row that matches Europe and online, which is 3,757. And again, we want to make there's a number without decimals. So there we go. This is the CMS feature. I hope it at some value for you. In the next lesson, we, in the next video, we're going to look at the average function, which is going to quickly give us the average. And like always, we're going to look at different variations of these function. All right, I'll see you in the next video.
6. AVERAGE(IFS): Hi and welcome to the fourth feature that we will be discussing in this class. In this video, we're going to look into the average function. The average function works pretty simple. You select a range, and all of this range here will take automatically for you the average. And just to be clear, I know that there's three types of every trees out there, which is median, mode and mean. But for media mode that we won't be looking interface and his class. Basically because the mean is the most commonly accepted and used type of average. And therefore, that's the one we will be using to. An Excel will also do that by adopting the average function. So to show you how it works is we just type average reopened do parenthesis. And we are going to select the revenue column. So what it does now it's going to take the average of all the numbers that are in column P, which is our deal size. And now we know our average deal size is 1,327,567. So next we're going to use the first variation, which like the other two videos that we've already done, is average. If whatever it is, you can take the average of numbers digital get based on certain criteria. So in this case, we want to know the average deal size of the deals made in Middle East and North Africa. And for this, we will start off by selecting the criteria range or 2s column a. We will type our criteria, which is it has to be equal to, and then we refer to that cell. We have right here formulas and three. And lastly, we're going to select the values that we want to know the average of. So to quickly recap, what we've done right now is that if column a, Mencius, Middle East and North Africa. So what we have in cell E13, if it equals that, then we want to take those values off those rows, off those seals entries. And when you want to take the average of them. So we get 1,356,754. And this is the average deal size if all the deals made in Middle East and North Africa. And now lastly, we're gonna use to some assorted average if's feature, which is slightly different. And it's the same approach you say method of making the formula as the sum function. So instead of starting with the criteria range which we did in the Middle East and North Africa criteria. We have to start with some average range, which is P, because we can have multiple criteria. So we want to know the average of column P, which is to deal size. And then our first criteria arranged he's kinda be region, which is a, and we want it to be Europe. And then our second criteria is going to be the online sales channel. You can add as many criteria as you want. The logic follows. So you add arrange yada criteria, you either a1, xi edit criteria, and you can keep going on. And in this case I'm just going to show you two. But just so you know, since we already put this average range at first, you can keep going with an endless amount of criteria range and criteria as possible. And for all the online deals that have been made in Europe, the average revenue was 1,308,358. So this is the average function. I think it saves you a lot of time. She obviously you can calculate the average manually by summing and then dividing by dt account. So that two lessons, the two videos we had before, but using the average function is gonna, yeah, cutaway sometime. I bet you guys spent and making different formulas. So in the next video, I'm gonna show you two minuss M max function. Very quick and simple features that you might want to use for dashboards or any other type of information at presenting sheet. So I'll see you in the next video.
7. MIN & MAX: Hi and welcome to the fifth feature that we will be looking into an RRR and our class. In this video, we will be looking into the min feature and a Mx future. And if you enter that min function and you select a range, it'll give you back the lowest value of the range. And then a contrary is you enter the max function would arrange it will give you back the highest value within the range. So in our case, we have a list of deals in our data sheet. Sales deals, and we want to know the smallest deal that has been made. What we do is we enter min, we open a parenthesis, we select our revenue column. And now it will give us back the lowest number. It can find indies column, which is $47. And for the largest deal, we entered a Mx feature. We go to our data sheet. We select P and will give us a deal of six, more than $6 million. So now there is also a variation that is where you can enter a criteria, like in the last videos we've done. So what we do is we min apes. We're going to start off by selecting the range of the deals. That range of, of which we want to have the lowest number, which is p. Then we're going to select our criteria range, which is a. And then we're going to write down our criteria like we did in the last videos. And right here we can refer to as cell that says Europe. So we're going to just write it down Europe. And we're going to close it. And now we get back that the smallest steel that has been made in Europe was $56. For the next one. The largest steel that has been made in Europe. We can copy it and paste it, and we can add max. Here. I set a mint and we will get the biggest deal that has been made in Europe, which is more than 6.6 million. And for the ifs, like I've also shown you in the other videos, you can add more criteria. For example, you can add the criteria where column H is within the last years. You can combine the first function we looked at in this class, for example, the today function, where we can look at what is the biggest deal or the smallest steel that has been made in the last seven days. And with this, you'll combine that in case you want to find the maximum value. So the biggest deal, you can combine criteria, which is but it has to be within the last seven days, for example, that it has to be a Europe. And with this where you can combine everything we've discussed in the first videos to get your biggest or smallest deal under certain criteria. In this video, I'm not going to dive into many different criteria you can add. But there's just so you know that this is not the limit. Alright, so that was it for this video. In the next video we're gonna look at the concatenate function and the different ways you can, you can write these down. There's different ways you can write down this function. And it's going to help you with cleaning up the data. All right.
8. CONCAT / &: Hi, and welcome to the sixth formula that we're going to discuss in this class. In this video, we're going to look at the concat, concatenated and the symbol. All three of them are very similar, but there is some difference amongst you can use this formula for different reasons. There's a million of reasons why you want to put text to get it. Because what these formulas do mistake and put texts together in one cell. One very common case is where you copy paste text from a Word document or from a PDF document. And you, the text is everywhere detects Susan, multiple cells and you cannot paste it. Sort of it looks nicely in one cell or edit. You can subdivide it in different cells. To organize this text. You can use those formulas. Seward groups all the texts into one cell. Let me show you this by using the concat formula. So we want to put John and dough into 11 cell. What do we do is we add concat, we open a parenthesis, we select a range, and we close it. But as you can see, we have jumbo without a space. So instead of this, we can add concat again. Instead of selecting a range, we select the cell. We add a space to it, and we add the other cell for the last name. And now we have Jane Doe with a space in between. These two ways of using this formula also show the limitations of the concatenate function. Concatenate used to be the one that has been there throughout the entire history of Excel. And concat is a new kid on the block that has been introduced a few years ago. The difference is that with concatenate, you have to select cells and that there's also a limited amount of cells that you can use. I believe the amount has been raced last couple of years to 240 something. And therefore, I don't think you'd reach the limit. But the concat function is unique. By that you can select a range which will be concatenated. And then third, we have to end function. And the N function is something you put in between cells, in between two references to text to add them to get it. So let's say we want to have bread with a space in between. We can add the nth symbol. We add a space. We again do the n symbol, and we add pit. And we have Brad Pitt. So those are three different ways of adding text together. And in this case we can make all the names complete. Alright, I hope you thought this video was useful. This is a very handy trick to know, a very handy formula to know it's not going to solve the biggest issues you can have with an Excel sheet, but they can give you a shortcut and not to have to do this manually. In the next video, we're going to look at the trim function, which is also very useful for comparing your data and cleaning up your data. And I hope this was useful lesson for you. And I'll see you in the next video.
9. TRIM: So in this video we're going to look at the trim function. And this is the seven formula we are discussing in this class. And the trim function is a function that really helps with cleaning up your data. The trim function makes the assumption that you don't want to have more than one space in-between words. You don't want to have spaces before the text starts in the cell. And that you don't want to have redundant spaces at the end of your text. So as you can see right here, we have different names like we had in the last class, same names but all written differently. So in this case in John Doe, we have two spaces in the middle. Jane Doe has a space in front of her name. Brad Pitt also has the space in front of his name, and I'd think Jane Doe evenness through spaces in the middle. Jennifer Aniston has a lot of spaces in between her two names. Hillary duff, As we can see here, has spaces after her name. Tom Hanks as spaces before he's named before the name Tom and in-between Tom Hanks and Tom Cruise has spaces at the end? No, sorry. Tom Cruise has two spaces into middle. In order to clean up our data and trim literally the spaces that are redundant, we can use the trim function, and it works very simple. You press IS opened a trim formula. Select the cell that you want to trip, close it. And there we go. Now we have drawn DO with one space in-between and no space at the end. And we can do the same for all the texts that we have right here. In a data sheet. Like if we would, for example, have this scenario, a data sheet like this. You don't want to have two columns just to have the names. So what we could do, let's say this is the column in our sheet where we have all our names. We applied a trim formula to all the names induce called column. What's recommended to save you the space again is to copy all the names that you have fixed, that you've trimmed, copy it. And here based in aims, and then paste the values only. So right now we have Alden names replaced with the trend versions, or you can get rid of this one so quickly. That's how you can trim your, your texts. Remove all the redundant spaces and it's a real good trick to clean up data. So in the next class we're going to look at the upper, lower, and proper formula to even help us better with cleaning up data. So I hope this is a handy video. The trim function really helps with cleaning up data. As the previous video, it's not gonna make wonders for you, but it can save you a lot of time knowing this formula. And it's going to be the same for it and mix video. So I hope to see you there.
10. UPPER, lower & Proper: Hello and welcome to aid formula we are going to discuss in this class. So in this video we're gonna look at the upper, lower, and proper formula. And just like the other videos to previous to that we did, this is really going to help you with cleaning up your data. And it looks, as you can see, John Doe right here, it looks like someone must have had issues writing this down. There's couple of those places that there shouldn't everything as capital letters. There's no capital letters. And you just want to make sure everything is written down nicely. For this, we can use the upper, lower, and proper formula. I'm going to quickly explain you what three, all three of them do. And then we're going to apply them to the name style we have right here. So you can see what it actually does. So upper, if you use the upper formula and you add a string of text, and you refer to a string of text in the upper formula. Every letter in the text string is going to be uppercase. Lowercase does the same thing, but everything is going to be lowercase. And proper is going to give the first letter of the word, a capital letter. Let me demonstrate this by applying it to John Doe. So what upper we just referenced to i2, which is the cell which contains John Doe. And it converts it into all capital letters right here. We're going to do the same thing here. So lower, open a parenthesis, select the same cell. And we're going to have John Doe without any capital letters. And what proper. Again, we do the same thing we referred to, same same cell. And here we see that the J and B are kept with others because stare at a first level is afterward. So let's apply this for all the names that we have right here. All of them are written in different ways. Some of them have some capital letters in the words. Some of them are complete capillary. There's some don't have capital letters. But if we replied These, all of them are going to be the same. So here, all of them have couple of letters here. None of them has couple of letters in here, as it should be with names. Older first letters have a capital letter. So I hope this was a useful trick for you. In the next video, we're going to dive more into a formula which is, which actually is going to help you calculate stuff and it's going to be a little bit more advanced instead of just cleaning up data. Because we're going to look at the if function. So I hope to see you in the next video.
11. IF: Hi and welcome to the ninth formula that we will be discussing in this class. Today in this video is going to be a widely used formula and one that allows you to go very complex. It's the if formula. The formula works very easy. You, you give it a equation. If this is true, you get a value for what you put in for through true. And if it's false, it's kinda give you, it's going to return you to value that. You want it to be one that's false. As it sounds right now, it's pretty simple, but as soon as you're gonna make different levels within if. So, you can put an if statement and for devalue, when it's true, you can put another if statement and another if statement. And you can go endless With this. It can become super complex. And you can combine it with other formulas. Like for example, if you look up. Now, now we're, in this video, we're just going to keep it simple. So we will live BY limiting ourselves to a very simple equation. This just gonna see how this formula works. But as soon as you start feeling more comfortable but using the IF formula, tried to combine it with other functions as well. So as you can see, we put down 10000, this is a reference we're going to use the cell references that we are going to use to make our formula. And right here you can see all the different operators you can use to build your criteria in the formula and the first part of the formula. So we're gonna do that to show you how it works. So we're going to write down is, if we opened our formula and we're going to select this 11000. So if an 11 is smaller than, let's say 1500, which we know it is. Then we want to have the value for when business true, which we're going to write down, right? And we want the value for when this is wrong, so wrong. And if we hit Enter, The answer is going to be right. In this text right here where I put right and wrong right now you can put anything you want. You can put another formula even if you want. But for now, just to show you how it works, we're gonna keep it simple. And obviously this formula works the same way if you replace this smaller symbol with a bigger than symbol. So the next one, it's pretty similar. We're going to just replace this. Instead of using the smaller than symbol, we're going to add the equal more to it. This means that it's not just smaller than what is going to be smaller than or equal to. So in this case, if 1000 is smaller than or equal to 1500, then we want to have the right. And if it's not, we want to have wrong. So we're just going to add is some symbol. And as you can see to demonstrate to you, because now obviously want 1000 is still lower than 1500. So this is not going to be any different from the one we used before. But if we would put 1500 here, this one is going to say wrong because 1500 is not lower than 1500, it's equal. And these are just going to say right, because it's equal. And since we added the equal mark to the formula, it's kinda give us write instead of wrong. So the next one is going to be, is not. So this is the smaller than symbol than the bigger thing than symbol together, which is going to stand for is not. So we're going to open up our formula. And we're gonna say, if 1500 is not, let's make a 5000 again. Then we want to have, let's make a true, just to show you that we can put different stuff here. And if this is wrong, as if this formula is not correct, then we're gonna put wrong. I'm sorry, false. I'm going to close it. Well, 1500 is the same as 1500, like this, reference is the same as 1500. Sorrow it gives us false. If we were to change it to 1499. And it's kinda give us true. And then the last, the last one we got is the, is the equal sign. And this one works the opposite. So if it's equal, then is going to give us true. If it's not equal, it's going to give us wrong. So let's put it there. We're going to open up the formula. If and 11 is 1500, then we want to have true. And if it's not, we want to have false. Alright, going to close it. And as you can see, it gives us false. Those two are opposite to each other, so they can never be true at the same time. So if we're going to change this one to 1500 issued switch, and we get true here and false here. So this is the if statement, bullwhip and three parts. In a very simple way. I'd recommend you to practice this yourself a little bit too. Maybe you have your own sheet open and you can practice with this formula as soon as you start mastering this formula. If rule really open up doors for you where you can build some more complex formulas. The next video we're gonna talk about the end an OR function. And this is something we can actually combine with the formula. So I hope I'll see you in the next video.
12. AND / OR: Hello and welcome to the tenth function that we're going to discuss in this class. In this video, we're going to discuss the formula and the formula. Or in the last video, we discussed how an IF formula works. And we've seen a formula exists out of three parts, where the first part is a logical test. Second part of value for if the logical test is true and the third part of value for one that logical test is false. The end an OR function can extend our if statement. Whereas the normal if formula has only one logical tests. We can have multiple logical tests using the N function that all have to be true. But with the OR function, we can give multiple logical tests and if one of them is true, we get the answer being true. So I'm gonna show you this with the following example. In this case, we are going to determine whether these employees get a bonus. So we have 127 person, person one through seven, and they have a certain amount of years of experience and they're certified order not certified. So to determine their bonus right here, we've written down that they need to have at least nine years of experience and they need to be certified. So as you can see, there's two different criteria for them to get the bonus. So we're going to have to use the end function. So we're just going to start off writing to if formula, you open a parenthesis and didn't apart where we have to give up the criteria for. If we're going to we're going to write and we're going to open up the parenthesis. And now we're going to give two logical tests. The first one is whether those three years of experience are at least nine years. So we're gonna reference to T12. We're gonna say, if it's bigger than or equal to nine. And this is our first logical tests, or with a semicolon, we can add the second logical test. And this is where we're going to check whether dispersion is certified or not. So we're going to reference to column you. And we're gonna say is yes. So that's the first part of the if statement. This is now two logical tests. And how are we going to get the value for if it's true, which is going to be 100 times the numbers, the years of experience, let's say to get $100 per year or for experience. And if they don't meet both of the criteria, both of the logical tests, the answer's going to be 0, no bonus. And as you can see, person one is certified but doesn't have the nine years of experience. So no bonus. For person two. It doesn't meet both the criteria, whereas it not certified and it doesn't have nine years of experience. Person three person have nine years of experience. Person for isn't certified. Person five isn't certified. Person six should get the bonus is let's see if the formula works and it does. This person gets a $1500 bonus and persons seven days no certify so doesn't get a bonus. So we see that the formula works. Now. We want to add the order formula so we're going to replace the end where we have two criteria and the person needs to match both of them in order to get the bonus. We're now going to replace it by or which is super similar to end. But as it already says in the name of the formula, if it meets one of the two. So if it meets logical test1 or logical S2, then it's going to get the bone. And so we'll see that a lot more people are gonna get the bonus if we're going to change this formula into war. And the setup for the aura formula is similar to. And so the only thing that we really have to do is use type or and now we can see that, yes, this person is certified. It meets one of the two criteria, and therefore it gets $300 bonus. This person doesn't it doesn't meet both of the criteria, doesn't meet the years of experience and is not certified. So this is correct. This burden person is certified. Dispersion is a sufficient amount user experience. Same for dispersion. And lastly, this person doesn't meet that criteria. So there we go. Now we have applied the nth and 4v function. You can use it for different purposes, of course, but I just showed you a simple example of why you would want to use it. And again, like I said before, knowing these formulas can really help you extend the complexity of your formulas and gain more possibilities within Excel by knowing more of the functions that you're going to have. It's a very easy function, but it's sometimes super easy to take away their very deep, deep complexity. Because if you don't have these formulas, you're going to have to put if statements within if statements within if statements, and it gets very complex. So I hope this was a very valuable video for you. In the next video we're gonna look at the function, which is a little bit the same to the nth in orange, the purposes a little bit the same. You can solve it by being more complex. But the function is just a very simple way of writing down your formula and helping you with, for example, so your statements. So I'll hope I'll see you in the next video.
13. NOT: Hello and welcome to the 11th formula that we will be discussing in this class. In this video, we will be discussing that not function. They're not function helps you to write down formulas. It works a little bit like the AND, and OR function that we used in the last video. But this one is more a matter of how you want to write down your formula. So what the function does, reverse your answer. So if you have a true not function is going to reverse it into false. And the other way around, if it's false, it's going to reverse it into true. So let me demonstrate this by Don here putting a formula. So not, we're gonna open up the formula. We're gonna write true. We're going to close it and now it's gonna give us false. So very simple. It's just reversing the logical test. So if we want to make it a little bit more complex, it's going to be 11 is smaller than ten. Well, we know that the outcome of this has gotta be false because 11 is not smaller than ten. But since we are using that function is going to give us true. So it's just reversing it. And it's just a different way of writing down, for example, and arguments that you put in an if statement. So to put this one to practice, we're going to calculate the same thing, the same exercises we did in the last video, which is calculating the bonus based on two conditions of person 12 personal at seven. In the last video, we've, we've already done this, but we did it without another function. And now we're just gonna do the exact same thing and we should get the exact same outcome. But now we're going to use a function just to show you how you can write it down in a different way. So we can start with the equal sign. We're going to write, if you're gonna open up the formula. And then we're going to start off by putting the end formula because we are going to have to criteria and it needs to match both of them in order to get the bonus. So person one has three years of experience and a certified. And just to remind you, the criteria are minimum of nine years of experience and the person has to be certified and then they will get a bonus of $100 per year of experience that they have. So the first logical test that the person has to go through is the experience. So using function means that we want to get the reverse outcomes. So you'll get the bonus when you use them not function. If you have less than nine years experience because then it's going to reverse the outcome into a true or false. Let me demonstrate this because it sounds pretty vague, but I think if I'm going to show it to you and it will make a lot more sense. So the re-entered the knots function as the first logical test in our end function. I'm going to write down. We're going to reference two to three years of experience and person one has. And if this is smaller than nine years, then it will get the bonus. But remember, we're using the nuts function, so everything that is not smaller than nine is going to get through. So that's from nine years onwards. So that's our first logical tests. And then the second one is going to be that whether the person is certified or not. So with them not function, we're going to test if the person has certified that is the answer is no, then we're gonna get a troop, but it's going to reverse it into a false. So we write die not, ride down not. We're going to reference to whether the person has certified. And if this is a null, then we're going to reverse it into yes, and we get a true. Alright, so those are the two criteria and, and now we can write down the value if the two logical tests are true, which is the 100 bonus, times the number of years of experience. And if it doesn't match one of the two criteria that we get, 0 bonus. And there we go. And we're going to drag down the formula for all seven persons. And we'll see just in the last video that person number six is to kinda get a $1500 bonus and arrest will not get any, any bonus. So formula's working again, it's just another way of writing it down. I hope. I hope this helps. It can help you for when you want to have a formula that gives you true if it doesn't match a certain criteria, instead of giving it true when it does match a certain criteria. So for example, if we, if we look at this part, the second logical tests that were at distinct or whether the person who certified, if we would write down, for example, this person matches in nine years and it better than uncertified if this person were to be certified, it would get the bonus. But now the logical test is testing whether it's not null. If it's a no, then the person is not going to get the bonus. But if it's anything else, then know the person who's gonna get the moments. So let's say it's maybe certified, then the person gets to bonus or in progress. Anything else that is not no person to kind of get the bonus. And a difference with the last video that we made is in the last video it has to be, yes. So think of a reason why this would be useful for you. There are some if statements where you can use this. There's also different ways of writing these down without using the math formula. But it's really a matter of preference. Alright, so that was it for this video. In the next video we're going to look at another feature which is going to fall into a category of cleaning up your data, organizing your data. We're gonna look at the right, the left, and the len function. So I hope to see you in the next video.
14. RIGHT, LEFT & LEN: Hello and welcome to the 12th formula that we're going to discuss in this class. In this video, we're gonna discuss three different formulas, which is the right, left, and Len formula. I'm going to start with explaining you what all three of them do individually. And then we're going to look at what it can solve for u. So the right function is very simple. You put in the right formula, you reference to a string of text. You add a number, and it's going to take the number of symbols counting from the right side from this text. So let me show you this. If we add right, we've referenced to this cell and we just want to have 567 instead of 1234567. We're gonna write down three because we want to have three, the three numbers from the right. And we want to leave out 1234567. Left works exactly the same, but as you could guess, it starts from the left. So if we would apply left and then with the same reference and all sort of number three, we would get 1-2-3. Instead of fighting examine. Len gives you return should a number of characters that a certain text has. So if we were to put in here Len, we open up the formula and we referenced to this cell and we close it, it's kinda give us seven because there are seven different characters. So what this could be very useful, there's a variety of reasons, obviously, well, you could use this, but what it mainly is useful is something I've seen a lot in my experience, is where you get different sources of data. And all of them mess around a little bit with the unique identifier. For example, the product code or portfolio code or any code that identifies your row, your client, your product, or whatever. Different sources can mess around a little bit with the formatting of this code. So applying the right, left and Len was going to help you to clean this up. So let's say there is, the starting code is 1234567. And then there's this one sheets where to add, for example, a letters to the end to match it with the codes that we have in the other source, which doesn't have the letters. We got to get rid of the letters in order to make Excel do its thing and see that those are the same so we can apply it to formulas to wake. So what we're gonna do is There's really depends how you're going to make this formula really depends on how the other formatted data looks like. So let's say there is codes that are 1234567, but there is also some of them that's a 1-2-3, 4-5-6, or doesn't necessarily have seven digits. In that case, we want to find a formula that's going to get rid of this bash and his letter for all of them, no matter how many digits that code initially has. We need to find a formula that's not going to get stripped the seven, for example, or six. Or we can apply to any code with any number of digits. So let's say if we have a 1000 codes, we can just pull the formula and everything's correct. So to do this, we can, instead of just saying left seven symbols, so it gets rid of the dash a. We're going to take away two digits from what we already had. In this way. If our code has like, let's say six digits are formula with left, 77 digits from the left onwards is not gonna work because then it's going to include the dash because that's going to be the seventh digit if we have a six digit code. So what we will do is we are going to start off with left because we do want to have everything from the left. Instead of saying now that we want to have seven, we're going to do calculate the total length. First we're going to reference to the cell. We're going to calculate the total length of this cell, of the amount of characters this cell has. And we're gonna to total length minus2 because dash a two digits. And that's the number of digits that we're going to take from the left onwards. And in that way, our formula would work in any number of digits possible. So we're gonna type length. We're going to open up the formula. We're going to reference to the cell. So it's going to calculate that it has nine characters. We are going to close this formula. We're gonna do it minus2 because those are two digits. Making the assumption that in every code there's always a dash and a letter and not more than one letter. We're going to close our formula and we will get 1234567. So right here we already made the combination of left and length. Now for the second one, we're going to apply, let's say our sheet is full of this kind of code, where to letter and a dash comes in front of the code. And we want to get rid of the a. We're going to apply almost the same formula, but there's going to be a small difference instead of left, we're going to use right because we want to count from the right onwards and we're going to get rid of everything that surpasses to quote from the right side. So we're going to write down, right? We're gonna open up the formula we reference to the cell and we're going to pull the same trick as we did in the previous formula. Minus2, going to close the formula and we will get 1234567. And right here we're going to have 1234567. And let's say in this case, we do always have a seven digit codes. So we're just gonna use the left function. I'm going to open up our formula. I'm just saying this to demonstrate to you how it works. We're going to select the cell. We want to have seven digits. You're going to close it and we will get rid of the Bash, a3, a3, a2. So now you know a few tricks how you can manage to clean up your data, get rid of redundant parts, or there's a number of different scenarios where you can use this. My advice is when you start using right-left And then combine them and tried to find a way that you can use the formula to clean up the biggest part of your data. As I said, it really, really depends on how it's structured, how your codes are buildup for which formula works. As I said, right doesn't always work. Left doesn't always work on its own and sometimes you have to combine them. But at least I hope that after this video you now understand how all three of the functions work so you can apply it on your own. I figure out the best solution for you. Right? So that was it for this video. In the next video we're gonna look at the PMT function, and I'll hope to, I hope to see you there.
15. PMT: 14 formula that we are discussing in this class, we're almost reaching the end. But in this video, glad you, you stuck because this is a very, very handy formula for people working in the investment or financial field or even for your personal finances. This really helps you to shortcut a pretty complex calculation. So today we're going to look into the PMT formula. And PMT stands for payments. And what it does, it calculates for you the annuity payments or in case you have an annuity loan, what the monthly payment should be. So there's different type of loans. You have loans where you have a fixed amortization. You have loans where you have a variable interest rate. Well, we are not looking into dose because they're relatively more easy to calculate. That annuity loan means that you're going to have a fixed payment, which includes both interest and repayment of your loan. And you're gonna pay the same amount every month with those two together. What happens over time is that the interest is decreasing. So every month, because you're also paying off your loan a little bit, the interest you're paying in dollars is decreasing every month because Gian standing loan is getting less and a repayment of your loan is increasing. That's usually what happens with annuity loss. However, It's very difficult to calculate the exact number. You need to pay every month to pay off the loan into preferred amount of years or months. Pnt helps you with this. So it saves you a lot of time. And it's just a very, very handy trick to, to quickly calculate the annuity you have to pay every month to pay off the loan. So in this scenario, we're gonna talk about a loan. I will get back to it later, but there's also worked for our type payments you want to calculate. For example, if you make an investment or or if you, for example, rent out a house, you want to know how many people, how many red people should pay you to pay back your housing? A certain amount of time. It can be for different scenarios, but just to keep it simple, I'm going to show you how it works for a loan. So this loan is $15 thousand. It's going to be paid off in five years. And the annual rate I emphasis and annual is 7.5%. Sort of first, solutes are first thing we're going to look for is we want to look at how much do we have to pay every month and annuity, which includes interests and the amortization to pay off the loan 100% in those five years. So what we will do is we are going to create a formula. And you know what for this? For this one, I'll show you the formula builder Xunzi will give you a better image of what we have to put in. So we're going to start off by putting into rate. Rate is the interest. So we're going to reference to dispel. However, these are monthly payments and as I said, the rate is annual, so we're going to have to divide it by 12. Then here we're going to put in the periods, so the number of periods that we're going to pay off the loan, instead of putting here five years, we're going to put five times 12 because we pay monthly. And therefore there are 60 periods. And a person value over loan. So we choose the, the money we're getting at this moment. And nominal value if this is 15 thousand. So now hit Enter. There we go. It's kinda give us minus $0.357. As you'll notice, it's a minus because it's a payment. And the 15 thousand Could we get our money that is that we are receiving and therefore as positive sort of payments that we make each months are negative. So if you want to know, if you were, for example, the person who gives out the loan, you don't want to see that you have to pay every month. You don't want to see a negative number because you're receiving money. So how you can solve this simply by putting a minus in front of the 50 thousand. And then you get payments of 300, which is a positive number agre amount. The reason you put a minus in front of the 15 thousand is because at the moment you give out alone, you give away the money out of your pocket and therefore it's an expense, it's minus. But then the payments become positive. And it this way payments, the payment function, the PMT function can be used in different scenarios or different use cases. So it doesn't only have to be paying back alone. It can also be giving out alone. But also if you buy a house or do other type investments, you can calculate the cashflows. Then if you want to calculate that, if in five years you have tape back half of the loan, how much do you need to pay? We're gonna apply the same formula. So again, PMT, we're going to select a rate divided by 12 because it's a monthly monthly payment. We're going to select a number of years times 12 because we didn't want to know the total amount of periods. And we're going to select a present value. But now we're going to add another argument, which is going to be the future value of the loan, which is the fourth argument we're going to give. And this future value has to be 7,500, which is 50% or 15 thousand. We're going to hit enter and we're gonna see that. Let me just revert quickly back to that we are paying back alone just to stay back in this scenario we started with, we're going to put minus 7,500. We want the balance to be minus 7,500 and we're going to enter, and now we see we have to pay $197 each month in order to pay half of her loan and fight heres. And then there's also a fifth argument. So I'm just going to add it to the one where we pay off 100% of the loan. We're going to press 0 because we want to pay back 100% of the loan. And then the fifth argument, as you can already see here, is going to be 0 or one. And this depends on when you do the payments, do you do them in the beginning of the month or the end of the month. And the reason why this makes a difference is because the PAD interests before the payments are after the payments. Every president 0, which should already was, we get $0.357. But if we press one, you'll see the mount is a little bit lower and it just really depends on the agreement you make with a loan, the investment, or whatever you are using this formula for it. Overall PMT. It's a very quick solution to calculate a complex enumerative, which can be very complex to calculate. Sorry, it's a shortcut. I hope this is a useful video for you. And the next video we're going to look into the VLookup function. And for me, and I think for many people in the field, lookup is for one of the most used formulas and are very handy formula if you work throughout different sheets and different taps. So I hope to see you in this video.
16. VLOOKUP: Hello and welcome to this second to last formula that we're going to discuss in this class. Today, we're going to look into the V lookup formula. Vlookup stands for vertical lookup, and it's one of the most useful functions, I think out there in Excel. I think it's also one of the most used once. And what our v lookup, the Vertical Lookup does, it's going to look for you for a matching value in a column. And it's going to give you a return to you a value from the same row and the column. So let me show you how this works. In our contexts. We're using a data heat where every row is another sale with all of them are unique order id and also a ship date. So let's say the company has a couple of complaints from customers who say, hey, my product hasn't been received yet. What we're gonna do is we're going to look for the order ID that the customers gave us. And now in our sheet we're going to look for when has this product being shipped? So quickly. Really going to show you the data sheet again. So right here in column I will have the order ID. All of them are unique. This is also something you have to have in order to use if you took a function because it, it's kinda give you the first mentioned result. And if you have more, if you have order IDs that are similar, you're not going to get returned all of the order IDs, ship date. So here we have order id and then in column L We have to ship date. So we're gonna instruct our formula to look up for the order ID and return the ship date that belongs to that order ID. Alright, so we got to start writing down v lookup. We open up parenthesis, and we're going to select that ID that we want to look for it. So that's this 1022. Then in the next step is to select a range in which it should look for this id. And in this range should also be to call him where we're going to return a data from. So it's gotta be I because I have all the order IDs. And then in ship date is L. We're going to select this range with the four columns we have here. And then the next step is we're going to instruct our VLookup formula. Which column is going to return our value? So column i is where we're going to look for. And we're going to write down four because one, J S2, k is three. L is for counting from the first column that we have selected in our range. We got, we want to get the data from, returned. And then lastly is true or false, true meaning D1 and approximate match. So do you want to approximate match with the order ID or do you want an exact match? Well, in this case, it needs to be an exact match. Because all the ideas, ideas are very similar because there's all numbers in the same amount of numbers, but we need the exact same order ID as we have in cell 022. The idea we are looking for. So we're gonna write false. Now we're going to close our formula or VLookup. Press Enter, and as you can see, our ship date is the first of August 2017. Now, let's just check this, double-check this. So I'm going to copy the order ID and we're gonna put it in the search bar. And as you see, we find that ID here. And let's just check if we got the right value returned. Yes, it's the first of August 2017. I know that I have written down the dates in the European way. So we would have written down in, in in the US for people in the US it would have been o slash 012017, but in this case it's August. And we can see is correct. So our formula's working and now we also want to know the ship date for the other IDEs. And now we can call the customers and say, hey, your product has to be shipped in 2017. While making this video, it's already 2020. So I figured the products and ECAN arrive anymore. But just for the sake of showing you how this formula works, you can look up very easily data from a big data sheet that we have. Alright? I challenge you to try to understand the field lookup function better as well, to master it and then combine it with things like the if statement or other formulas that we have discussed before. Because especially if you have learned all the formulas that we have discussed so far and you're gonna combine them. You can really make some very, very powerful formulas in Excel. Alright, so next video is already our last formula that we are going to discuss is the error function. Not a groundbreaking formula that's gonna solve the biggest problems for you. A very handy trick to avoid getting errors. So I'll see you in the next video.
17. IFERROR: Hello and welcome to the last formula of this glass. It's the if error function. The error function gives you the opportunity to give a different value than the error if the outcome of your formula is an error. So I'm quickly going to demonstrate this to you by giving you the following example. We have some ID numbers here. And as you can see, one of them is a bit longer than the rest. I just added some numbers and I entered a VLookup, which we have discussed in the last video to look up the profit of these IDs. So for example, we have the first two products, we correctly get back the profit that we have been looking up in our data sheet with the Ford product, also, we get the correct profit, but in the third one, are VLookup gets an error. The reason we get this error is because it hasn't been able to find this ID number in our list of ID numbers. Let's say this is a very long list, so ID numbers and you want to have the total of this profit. You're not gonna get it because there is one of them that has an error. How we can solve this is by adding the error formula. So we're gonna go into the formula. We going to, in front of everything we have in this formula, we're going to write if error. We're going to open up the parenthesis. And then at the end we enter a semicolon. And now the first argument of this, if err is the formula that we want to have in this cell, and if this isn't error, we can give it a value, which is, let's just say 0. And now if this VLookup formula that we're doing is an error, we should get returned instead of the error is 0, which in this case we get. And therefore our total sum is working again. So if error is a quick way to avoid this, you would just have to apply it to all the, the whole range, the whole column you're adding news where you're looking up this profit. And then in this case you'll prevent yourself from getting reduce errors because it cannot sum, because there is an error in the range that you are summing. There's different reasons why you want to use the if error as well. The only thing I would have to recommend you for using this formula is that if you're not sure if you have been correctly entering your formula and you're getting an error because you didn't close your parenthesis or you typed in a value that the formula doesn't understand. You won't see the error because the error formula. So just to make sure, when you use this formula, you have to make sure that you really get the formula that you put in the if error, right? Because you're not gonna get to feedback if you put in a wrong formula. Alright, so that was it. That was all the 15 formulas that we have discussed in my experience, but also overall experience of people using the Excel. These are to 15 most useful and most used formulas out there. I hope this helped you to advance your Excel skills. And as you can make some beautiful, powerful sheets in Excel using those 15 for us. Thank you for following along with my, with my class. I hope it was useful for you and I hope to see you in my next class.