Transcripts
1. Advanced Excel: Top 10 Formulas to Work Smarter: Hello. My name is they Look around me. I help companies use Excel better by providing customized training and implementing dashboards and enhance visualization techniques. In this Excel course, I will show you how to be better, faster on design, solid spreadsheets that will impress others. When I first started using Excel on, I got introduced to be Look up, I used it in every situation and many times I had to change the set up of my data on my table to make it work. That was so frustrating, but it never even occurred to me to find out if there better ways of doing things. My main goal in this training is to increase your awareness off the top tools and formulas in Excel to help you solve complex problems. It's only when you know the extent and possibilities Exel offers you. Only then are you able to handle any type of analysis using the best method. Now there's a handful of Excel formulas, which I believe every user who deals with Excel on a frequent basis should know off in this section. I will teach you of a look up in case you don't know it. But I will take you beyond the look up and look at other extremely useful functions that can handle more complicated lookups for faster analysis is with these functions. You're well equipped with the knowledge you need to handle any situation that requires you to analyze data or grab a piece of information from a large table. Learning to be fast and effective in Excel takes time and practice, so please don't skip the exercises. I also know that technical training can be boring, especially online technical training, where you're just staring at someone screen. So I've tried to bring interactivity and this live teaching atmosphere off a classroom into my online training to make sure that you benefit. Are you ready to learn some advanced techniques? Then let's get started.
2. Formula 1 - SUMIFS, COUNTIFS, AVERAGEIFS to Account for Exceptions: did you ever need to provide an answer to this question? So you were told to provide a total four, for example, your sales, but not just to fool some only for a certain product, a specific month and for a specific country. Now, basically, if you ever need to make us some with exceptions and especially if these exceptions are more than one thing which is normally the case in real life than this some if s function is for you. Notice? I'm not saying some if, but some if s now, you might already be familiar with this some if version just not with the plus s one. But it's actually popped up since Excel 2007 and surprisingly many people missed it. But it's actually such a time saving function, though. Now, what's the difference? You might ask? Well, some if can only check for one exception, whereas the if s version can check up to what I believe it's 29 criteria. Now that you'll ever need that much. No. In this lecture, I'm going to show you the summit function as well as the sum if s version the average if s is similar to the summit fest except that it just checks for the average instead of the some count. Defense is slightly different. I'm going to show you that as well. If you've never used to some if function before, I recommend that you don't learn to use it now and just learned if s version. The reason for this is that the if s version can do more than the if version on. Because the syntax of the two differ. You don't always have to think which one should I use Now? I personally use the FS version. Having said that, I'm going to start off our example by showing you the sum if version of it and just explain to you y d defer and how they differ here we have a month's products country and sales revenue from this data would like to find our total sales revenue for these criteria. So the first announces we want to do is how much revenue did we have in February? Because it's just one criteria were checking. I can use to some if function the sin taxes that first comes the range which range. It's not the some range because the some range is at the end, So the first range here is actually the criteria range. So it's the place where this criteria can be found, and that's basically here. So I'm going to quit here and then use my short, crikey control shift down. Next is the criteria itself, which is this one and then is the sum range. So I do the same thing. Control, shift down and let's check that. That looks right. Okay, so that was the sum If function very simple, you can see that the some range came at the end. No, we have to create area that we want to check. So we want to get this some if it's February and if our product is shirt quite. And this is where to some if s function comes into play. This function was introduced since Excel 2007 and it made the lives of so many people so much easier because they no longer had to use conflicts, array formulas to do this type of calculations. They could use a very simple if s formula. And the way it works is that now the some range is at the beginning, because when my herself program this formula. They couldn't leave the some range at the end because they don't know how many criteria you're going to be putting in your formula. So you had to come to the front and obviously they couldn't change the old formula because then it wouldn't be compatible with the older Excel versions. That's why this new function was introduced. So are some range is our revenue. And next is the criteria range one criteria one criteria range two criteria to and sword, depending on how many different criterias you have in this case, we have to. So the first range of our criteria where we actually find February is this one. Then comes the actual criteria. And now we move on to criteria range too. So where can we find this in here? And this is the actual criteria were checking for Let's check that as well. So we have fab shirt. White. Is this one these four tour to aid tour to eight? No. In the next example, we've been given a further criteria to say February shirt white and the sales that we had in the U. S. So we do the same thing I'm just actually going to copy this. Just move these ones down the ranges, air the same. So all I have to do is add an additional criteria for U. S. A first is the range where I can find it, and then is the actual criteria itself. Okay, 16181618 One other thing I can do is to use wild cards and comparison operators like bigger or smaller or a combination of thes. So, for example, if instead of shirt wide I was actually looking for the text in there's have to put quotation marks and put shirt w Asian, then put the star sign quotation marks. It finds it. They were as if I take that away doesn't find anything so I can use this sign to find anything that starts with shirt and then has wh in it. Now let me show you how to use the comparison operators. So if we have this, But instead of USA, we want to add the sales revenue only if it is greater than 500. If it's not greater than 500 it shouldn't summit. So now let's write that one. So we do so Me fast. Some range. Still our sales revenue. Crazy area one. It's February. Create area to her products. Okay, now we come to criteria. Three. So we want to Onley some if it's if the revenue is greater than 500. So in this case, what's our crate area? It's the revenue. It's this one. So you can be or some range. But it can also be our criteria, and the criteria itself is that it's greater than 500. Let's check that. So we have sure, Dwight, this one's that This is not greater than 500. This is yes, and that CS less. That's March 112611 to 6. The only thing you need to keep in mind when you add the comparison operators is that you need to put them if you If you're not putting it in a cell and you actually want to put it in a formula, you can't do this. Get an error. You need to put it in quotation marks because it's like text. You don't have to put necessarily your number in the quotation works. You can also do this, or you can have your number in a cell and do a sub reference to the number like this. So basically have the greater one in there and then and connecting it to this cell. The case of just still forget when you're adding these operators, put them in the quotation marks. That's the summer fest function. The average if s works in the same. Exactly. The only difference is that it takes the average instead of the some. So in this case, you would say average if s okay, it's taking no the average of the revenue for Feb and shirts white. The count is slightly different because you don't need one of these criterias. You don't need that some range. So let me show you the count. If first you just need range and create area. So we're just one account. If in this range there is February so how many occurrences of February we can find here? That's a range. That's our criteria. Eight. So we have February 8 times. That's right. Now the if s version allows us to check for more criteria and count based on these. So we say criteria range one is the same. So that's our month secretary arranged to. That's our products. So four, basically we have four occurrences of February and shirt white in here. It's this one, This one Where's the other one in this one and the same for this. We have no three criteria to check again. You do the same thing three, one to and three did. Anything I'm going to show you is the advantage of excel tables. So let's say in this example someone tells you, Oh, we were missing something in February. You have to add this number. So I'm just gonna put a big number. Let's say this one. Obviously, your values here in your report are no gonna update because they're not referencing this new line. You will have to manually expand these, but to avoid that, you can use excel tables and they sell tables are great for such cases. So all you have to do is click anywhere in a cell and press control T. This is the data for a table, and it has headers can. Now it's an official table. And you know that once you get this table tools activated here and now if I add my new data to this so short, white. My numbers are updated automatically. Let's click on one, and you can see the range was updated dynamically. That's the advantage off Excel tables. Now. I generally never like this design that it gives me so to remove that you just go to design and you said that clear. You go back to your own formatting off the table. The other needs think about this is that if you're table is really long, you see that it changes your Excel column headers to the table Headers. When you scroll down says kind of like a partial freezing pains, it only does it for here. And once you are out of your table to see the usual column headers, as you can see that if S version allows you to easily add exceptions to your some average and count calculations, but don't make your life more difficult by trying to use Excel filters to get to your some use, the FS version instead knows return. Go ahead and do the exercise for this lecture
3. Formula 2 - IF (Nested Functions) Because Data Analysis is Often Not Simple: do you use if in your daily sentences? So when you speak to your colleagues, your boss, especially to your boss, Well, if you do, your most likely need to use if in your Excel files as well. But you probably know the if function from a basic Excel training. But you might not be familiar with its use in met as nested formulas meeting using more than one if in a formula. Because the reality is there are always exceptions to the exceptions. I have yet to see a company where the analysis and structure was actually simple. In this example, we have our sales agents, the quantity it is sold on the revenue they got. And we'd like to make some analysis based on this. So we'd like to flag the sales agents that sold three or less. That's our first step, and we're going to start over there if function the first part is basically what we're checking in this case, we want to check if quantity is less than or equal to three. So we want to check this number, and if this number is less than or equal 23 then what do we want to do. If it's true in this case, I want to flag them. So I actually wanted Type the word flag in there. I have to put it in quotation marks. I could also do a cell reference and reference the agents if I wanted to have their name there. The last argument is what to do if it's false. So what to do if quantity is actually greater than three? In this case, I don't want to do anything. Leave the cell's empty because I'd like to filter on or make analysis on the flag agents for this case. I should get nothing, because that's what I put in there. Nothing. But for this one, this one and this one, I should get a flag, which I do. And don't forget that the difference between the if function alone and the summit version is that with if you have full control to change the entire outcome of the situation, whereas with the some, if you're restricted to summing that outcome, let's move on to our next analysis. We want to flag if the number that they sold is less than or equal to three, and they also made money that was less than 200. Whenever you have your sentences haven't end in it. Your formulas will have an end in it too. We will use if, for a logical test. We're gonna use the end function because we're checking for two things. In this case, one is quantities of the 1st 1 Is this cell less than or equal to tree? And if revenue this one is less than 200 close bracket. So that was our test. What should it do? If this is true, it should flag them. If it's false, Do nothing. No. We get flagged for these two only because this one has a revenue greater than 200. Even though the quantities less in a similar way, you can also use the or function and or would check if either off these criteria is meant. Then it was flag moving on to more complex analysis, which is actually more or less the case in real life. So here we want to see if revenue is greater or equal to 300 if quantity is more than four and we should give a gift to these agents to stay with me here. Okay, otherwise, if revenue is grated into 50 we should praise them. Otherwise we leave it empty was start writing this. We start off with an If, don't forget, your formula is actually very similar to the way you would explain something. You know, like if this is the case, then we do that. Otherwise we do that. It's the same flow in the formula. It's just without the text. So how would we start to write this sitting? Haven't if and here I haven't. And so if both of this are met, then we're going to give them a gift. I start off with the end. Revenue is greater or equal to 300. Quantity is greater for equal to four. That's my logical test. What should it do? If it's true, we should give them a gift. Now, If it's not true, what should it do? We should check again. Is revenue greater than or equal to 2 50? And that's another. If so, then check again. Is revenue greater than or equal to 2 50? And if it is, what should it do? We should praise thes agents, Otherwise leave empty. Do not think we always take care of your brackets, many two brackets and there's I just accept. And that's my formula. It's test in this case is Tree 100. I'm selling more than four. It's a gift. This is a case for a gift, and that's a gift as well. These guys don't meet the criteria, so we leave them empty. Mr. Formulas can be confusing. You might not get the results right away like we did now, so you might run into some errors and you need to do some debugging. So this is where my previous tip comes in. To debunk this or to check how this is working, we can go to formulas and evaluate this, some violating to sell. That's giving me gift. Here we're checking if C five is greater than this is actually translating that C five to the number two. The value in the cell and in this case is 300 greater or equal to tree 100. Yes, it's true. Is six greater than four? That's true. So the result of my first part is true, which means that my answer should be gift. If I go to check. One of thes empty ones is 210 greater than 300 no is to greater than for new. This is false, So basically it's false. It has to move on. It's nothing. Here is an error. It's moving on to check. The 2nd 1 is to 10 greater than 2 50 No. So this is going to be an era's well, so India and what remains is nothing. So don't forget to use the evaluate formula. If you're not sure why it's giving you a certain answer. It really helps, especially with nested formulas. Now I'm also going to show you another use off the if function. I got this question yesterday on Cora, and I thought it would be good to include it here. I just made a simpler version of the question. What? The person was looking for Waas to get the foul path based on the folder structure off their files, and they used the concatenation function, which basically connects the values in one cell to the next. So they had these and what they did is because they wanted the correct folder structure with e slash is in there. They added the slashes between the names. Okay, so, like this, you see that It's reporting Excel, and they did the same for the rest, he said. That's what they wanted to achieve. Their problem was they didn't want the forward slash if any of these were empty, they wanted their name to start with the name off the folder and they posted this question and there was one answer on there that showed them how to do it with VB a Andi, I figured that maybe the person doesn't want to use V B A. And why use VB A. When you can use a simple formula and the if function is actually great for this, how would we use it in there? But instead of fixing this, slash will create an if for it and will say, if there is no value in here, give us nothing. Otherwise, give us a slash or you can do it the other way round. If there is a value in here, give us a slash. Otherwise, give us nothing. So we were placed this. What if this is nothing then and I think otherwise, give us a forward slash and depending on how many of these could BMT you just repeat that for the other slashes. So in this case as well, we say, if folder to is nothing, then nothing otherwise forward slash Okay, lets say Folder three always has to be filled so we can leave that empty. And if I copy that down, I get it to work fine. No need for V B A or anything complex. To recap. The advantage of using a nested if function is to make exceptions and change your outcome. Based in a situation now it's your turn to practice.
4. Formula 3 - IFERROR for Credible Reports: the if error function provides an elegant and simple way to hide errors in reports. If you're wondering, how does an error look like while it's ugly, your boss will be very likely to point that out to you. But here's the report that has no errors. And here's one with errors. If you take this to your boss, he's going to tell you there's something wrong with your report. You made a mistake. Are you going to be like new? It's so right. That's just excel. It's how Excel behaves when there is no data here or is dividing by zero. Don't even try because your credibility is long gone and also know that errors come in different forms. Here's an overview. A man. Let's have a look at how we can handle them in Excel. If you've been using Excel for a while now, you might be familiar with the is error function from Excel 2007. Microsoft added that if error formula, which is a much more elegant and faster way to right this type of formulas, so say goodbye to is error. Now, if you've been using it on, let me introduce you to the if error formula. In this example, we have our customers quantities sold in 2016 and contrary sold in 2015. We'd like to calculate the percent of change. Now you can see that we have a problem here and here. These are two different errors. This one is because it's been dividing by zero MT. It gives you a def zero and this one. The hash value is because we have a text that we're trying to divide this number with, and obviously this is not gonna look credible. If we print, decide and give it to our boss. He's going to think that we've made mistakes in this report. So instead of going in and deleting these manually, we're going to keep them in. But we're going to include them inside. And if our formula and it's really, really simple, all you have to do is you start your formula with the if error function. So you basically type in your formula right after the bracket, and then you just have to specify what while you do, you want to see if the results of this is an error and let's say in this case we want to see nothing, and that looks much better. Generally when you're designing reports, you will want to have most of your formulas left inside. And if error function because it makes sure that in case the results of your formula is an error, for whatever reason, it's not going to show in your report and noticed that the if error function doesn't differentiate between the type of error, whatever it is. If it's a value or a devora ref, it's going to give you this. In my case, nothing. I could also put something like Check this if I wanted No, just write this down. You can see these become. Check this. So it's whatever I want to see in case my formula returns an error. In this case, we had a simple formula. A lot of formulas you're going to be working with are probably going to be bigger, more complex. And my approach is to always write down Lee formula first on when I'm done, I then wrap it inside the if error formula. For those who are curious, how people use the is error function and why I'm saying say goodbye to it, just going to show you an example in that. So if I use is error and we type in our formula here, the answer is false or true. If it's an error, you get true. It's not an error, it's false and how it was used before. Waas with an If so you say If this is error, then what should it do? If it's an error, it should put nothing. And if it's not an error, it should provide the result of the formula. You get the same result, but you can see that this formula is just much more complex to write than this one. That's the if error function, an elegant and simple way to hide errors in your reports, notes return to do the following exercise.
5. Formula 4 - VLOOKUP & HLOOKUP For Dynamic Lookups: I had to include the V, Look up on the age, look up functions here, even though I think most Basic Excel users are familiar with them. But it's a function that's kind of between basic and advanced. So I thought in case you missed it, I'm going to include it here. Now, the main purpose of these two functions is to look up a value in a big data table and give you a Chris ponding value back that's sitting in the same room. Now, one thing to keep in mind is that the value that you're looking up has to be on the left. Most column and the value that's being returned has to be on an adjacent column. The left is the key word here, so you can't look up a value on the right side and return a value on the left side that we look up an H Look up work in the same way. Except that we is for vertical lookups, and H is for horizontal lookups. So if your data table has column headers, you're gonna need we look up. And if you're data table has role headers, you're gonna need a choke up. So that's having look at an example for each for this demo. Imagine you've been given a large set of data and you've been told could desert the sales agents. The quantity they sold the money, they got the price they charged on their customers. What you like to do is to create an overview agent report where you select agent and you find the price and the customer from this table. As a first step, we're gonna add our drop down. It's what we learned in an earlier lecture on data validation. So we're going to go to data data validation instead of any value select list. And the source is our agent suppress control shift and the down hierarchy. And though we get to select our agents from here, therefore the agent with select would like to find the price. That's where are we look at Formula comes in. The first argument in the re look up formula is the look up value. What we're looking up in this case we're looking up the agent, which is in this cell. Next comes the table array. You have to remember that this is the full table. So the full range where you're look up, values in and your answers air in, and it's one range. So you're not supposed to select different ranges by holding control down and going like this. But instead you select the entire range, which in this case, is from here to here. I'm really press a gain control shift down. That's my range and keep in mind. But the key? The main thing is that this Agent D is the 1st 1 in this range. It has to be on the left hand side, even if I don't need quantity and revenue. I have to highlight them because they're in my way there in the way off me getting to price and customer. So you always have to sell like the entire range, even if their stuff in there that you don't need next is the column index number. And that basically means how much should it move? So it assumes that the 1st 1 is a 12345 So, basically, in which column is the answer that you want, we want the price, and that makes it 1234 The fourth column. We have to put foreign there. And this last argument is important to get right. If you're look up, value is text and not a number. If you leave it empty, it defaults to true. Which means that an exact match is not necessary. But it requires that your look up column is sorted in ascending order. If it's not sorted in ascending order and you leave this argument empty, then you're probably going to receive an hour. In our case, we have it sorted as ascending. But generally I don't want to be border to think is this text is It's the number isn't sorted or note. So I always put false in here, meaning that these should be an exact match. I recommend that you generally put this on false A swell. Okay, so let's check this Agent D. The price here is 39 rounded 39. No. What about the customer? I can follow the same logic again. My look up value is Asian D the table. I'm looking this up is here on. My customers should be included. Which column is the customer? The last one, which makes it 1234 The Fifth column and false as the last argument. Customer D for Agent D. Customer age 37 for Agent H. I have a very simple, dynamic report using Be Luca. You can also use V Luke up within formula so they don't have to be used on their own alone like this. So, for example, if I wanted to find Price here and let's assume I didn't have pricing, my source data actually need to calculate it. I could write a formula on this and I could say, Well, divide the revenue by the quantity and to tell it which revenue to divide, by which quantity I will use. The we look up formula and say, Look up, Agent age in this area, I want the revenue first. So that's number three False as the last argument. And divide this by the results of the Blue Cup formula for quantity. Quantities number two. And that gives me my price. Agent age is 37. As you can see, V look up doesn't have to be used on its own but can be used within other formulas agents, and it's 42 customer in. And now let me show you the H look up version of this So in case your original source data is not provided in this way, but instead in rows. So I'm going to copy this. We're going to move down here. I'm gonna paste this as transposed. So in case you have your data provided to you in this way, which is less likely, but it's possible. And you want to do the same type of look up in the same type of overview report, you will have to use the H look up function. So, in the same way, if you have our sales agent, let me just copy this down. We write new formulas, so in the same manner, if you want to find the price off agents and from this table, we're going to write h look up to look up. Value is this. Our table array is this. Don't forget that what you're looking up here needs to be the first in your table array. Next is the role index number instead of the column index. So where is Price? And here 1234 It's the fourth row and again do false the same with customer. I can actually copy this paste this in here because this remains the same. That's what I'm looking up. The table is the same instead of four. What number do I need? Five. Customer in pigeon peas here but at the end forties. May Price and customer P is there. Get if, instead of price. I wanted to find quantity. The only thing I need to change is my index number and quantity is number two. So I get eight. That's the age Look up function to recap One major limitation off the V Look up and the HQ Cup formulas is that they can't look to the left. So what if he wanted it the other way around that instead of selecting the agent here, we were going to select the customer and get back to agent here. We wouldn't be able to use the classical villa cut formula. We're gonna need to have to change the table by bringing customer to the left hand side, because the value we're looking up always has to be on the left hand corner. Now there are other ways of getting around this by using other functions inside the villa cut formula. But don't bother to learn these because in the next lecture. I'm going to introduce you to one of my favorite look up formulas, which can look to the left and to the right and also do matrix lookups. But before we get there, it's your turn to do an exercise.
6. Formula 5 - INDEX & MATCH for Flexible Lookups: If there's one formula that you're going to take away with you from this training, it should be the index a match function I'd really like to see you use dysfunction in your existing fouls because it will save you a lot of time and frustration. The little Yellow Cup formula has its limitations, and that's where the index a match formula, comes to the rescue. Why is it so great? Well, the A mix and match is less prone to errors than we look up. It can look to the left or to the right on Lake Villa Cup, which can only look to the right. You can also do a two way look up by looking along the rows and along the columns in a matrix to find the intersection. The performance used to be a factor in the older Excel versions, but it's not so much anymore. Remember, Index and Match is not one formula, but it's two formulas used as one. Here is an overview of how the index function works and the arguments needed. You basically index or highlight the area where your answers and you use the argument on how Maney rose to move down And how many columns to move across to find the location of your answer matches the function you use to make the index function dynamic. Basically, much returns the position of your answer to the index function. That's a broad overview. So now let's get right to it and start off with the index function. I'm going to teach you index and match based on the same table. An example that we had for the V. Look up. So if you remember in the last lecture we looked at the Villa Cup and we had the table in the sales agents quantity, price and so on, and we wanted to have an agent report where we actually selected the agent. And with we look up, we found the price that they charged on the customer that they have. We're going to do the same thing now in this example, except we're gonna use the index and match. First, I'm going to show you the index function. So let's go here and start off with index. No, The first thing you need to remember is that right after the bracket, the range that you put in has to be the place where your answer is in. So in this case, we want to find a price on the range for that is here. After that, I need to specify how many rows do I want to move down In this case, I'm going to do it manual right now, So I'm looking for Agent A and that's one. Starts from one's index is 12345 and so I want one. How many columns do I want to move? Nothing. I want to stay in this column, and now I have the option off putting nothing. I can also put zero, or I can also put a one. They all mean the same thing because I only have one column selected. So now let's have a look at Agent D. Obviously, this is not going to update because it's not dynamic yet. So in this case, I would say Agent Deace four is the fourth location. I'll have to put a four here. Andi, I get this now. What happens if I put instead of one for the column? If I put it to they get an error. The reason for this is I cannot move outside my index range. If I have only indexed one column, I can only stay here. I can't move out of it. If I thought my answer, let's say was also in here I can put it to So here have index. Know this area. I say Go down four rows and it starts from here. So it's 1234 and move two columns 12 gives him back 39. If I switched the column now toe one, he gives me 3 50 my revenue. So you see how this index function works on its own. Let me put these back so I won't may range to be No, just the price. Four and one is fine. So obviously my aim is to make this one dynamic instead of typing in the four. Because looking up where this agent is and doing this manually, I want the formula to figure it out, and that's where the match comes into play. I'm going to write it here so that it's clear what match does on its own. The one thing you should remember with match is that it's not a normal formula in the sense that it doesn't provide you with the value in the cell. You know how you can make formulas and you say, like equals this sell The number that's returned is like 39. And if I referenced this cell, what's returned is find price. Match doesn't do that. What it does is that it returns the position off your value. So let's have a look at how it does that. I want to get the address of Asian D in here. My look of value is Agent D. My look up array is like the range where Where can I find Agent D agencies somewhere in here on the last argument is if I want an exact, much less or greater stick to exact match. So don't forget zero as the last argument for the match function. I get the four and the four means it's the fourth place in here on what you need to take care off with the match function is that the range that you select has to be a one way street. It has to be either one column or one rule, because match can't do this. It doesn't know should it look this way or should it look, this way because it just returns the number. You need to have a one way street and you can do it across a rule as well. So just to demonstrate if instead of this range, if I had it like this, what number should the formula give me in this case? And one Because Agent D is the first position in my range here. So now you see how the match function works. So instead of the four I'm going to say match what do I win a match? Where is this agent? In here. I want an exact match. The rest I can leave as is just Instead of this four, I put in a function to make it dynamic. I get 39 which is good. And now, if I would select, it's a agent Jay. I get 40. If I select Agent G, I should get 88. That works quite well. And now in the same way, I can like the formula to find the customer. What should I index the customer? This is the area where my answer is in. And that's what I need to index. How Maney rose. Should I move down? Well, it depends on where Agents G is sitting. And how do I make this dynamic? I use the match function. I'm looking up Agent G. I'm looking him or her up here. I want a perfect match, the last argument because it's just one column. When my answer is in, I can leave it out. Customer G. Let's switch that works well. And right now you might be thinking, Why would I ever use the index of match function when I could use a more simpler Villa Cup function? In this exact example, I would also probably use the Villa Cup function. But if my table was a lot bigger than this, so like a large system data extract and I needed to look up a value here and get back an answered, I was sitting far on the right hand side. I'm going to use the index function because Villa Cup is more prone to errors. I'd have to count the number of columns I need to get to mine. And if I happen to delete a column in the middle, I'm gonna have to revise, maybe look up and I might forget, but I wouldn't have to if I use index and match. So that's one advantage, right? There might no be fully convinced. So let's move on to the next example here. In this case, we want to find our customers, select our customer here and get our agent back. Remember, we couldn't do this with the V. Look up because we look up can only look in one direction. So we would have to cut out this customer column, put it here and then. Right. Harvey, look up. In this case, we don't have to touch our data table, so let's just use their index. What answer do I want here? I want an agent name. Where can they find that in this column? Next. My room number. How much should I move down? Well, that depends on which customer I want. So that's why I used the much function I might look at. Value is the customer. Where is this customer? This is the range where I can find it, and I want an exact match. Do I need to move any columns? No, I just have one column. So put 01 or leave it empty. Close the bracket. Agent E was checked that it's fully dynamic. That works quite well. So you see, in this case, I don't have to touch my data table. I just have to write my index that much formula. Now, let's move on to another case. I want to select my agent, but I also want to select my variable in this case by variable. I mean, I want to define if I want quantity, revenue, price or customer. Let's just make a quick drop down for it is Okay, So I basically want to select my agent, and then I want to select. Yes. I want to look at the revenue of this agent or the quantity that this Asian sold and have my answer here. This means that I need a two way Look up. I need to look the agent appear, and then I need to look the variable here a swell and get the match index. Now what do I index in this case? Work in my answer. Be Well, it can't be here. Can't be agent. They can't be an answer. But the answer could be any of these because depending on which variable, I select quantity, revenue, price or customer. My answer would be one of these say could be any of this. Next is row number. How many roads do I want to move down? Depends on where my agent is sitting, and that's the one I need to look up. I looked them up in here. I want an exact match and always be careful that the ranges that you select so that your index range and your match ranges they match in terms of the length of the range. Because remember, the match function starts counting. 123 here said they had. It has to be in line with your index. Strange now for the columns. What should I do? I need to do another match and I need to match this variable in. Now I'm going to select the role here. Zero for the exact match. Close bracket. Close bracket again. 3 50 Agent D Revenue 3 50 Agent E. Customer. Customer. E You see It's fully dynamic and works both ways. Now just let's have a look at the evaluate formula just to use some of the tips we learned here. Let's try to analyze this in more detail. So this evaluate this one we have index it's trying to match Agent E. The answer for the Rose is to move down five notes trying to figure out the second match customer. The answer is four. So basically what it's doing is that you have this range. You're moving down. 512345 and four across 1234 and you get customer e. So don't forget to use evaluate formula if you get stuck with your formulas.
7. Formula 5 - INDEX & MATCH for Complex Real-Cases: now moving on to more complex example. Well, let me not. Carly Complex is actually a more real life example. More practical cases that you're gonna have because the fouls that you're gonna have, let's admit they're not going to be all perfect. With just one column header, one roll header, you might have cases where you're gonna have multiple column headers and a combination of thes. It's what going to make them unique in this example. I have actual quantity, actual revenue and then budget quantity. Was that revenue and so on and you could even have three. So you could have actual January quantity, actual February quantity and then so on for each month. And you could also have many role headers. In this case, I don't. But if you do, you have to use the same technique. I'm going to show you here, and I'm not going to show you one technique, but three different techniques on how you can do this and what were actually aiming to do is that this is a really data table and we want to get a report. So imagine this is a nice fancy report on another page of Excel file that we want to print out. What we want the user to do is to select agent from her list on the report provides the actual quantity, the budget quantity and so on from this data table. Now, as you can see, I foot method 12 and three down here. The order does matter, because I start with the simplest one First. If you're not that familiar with the index and match formula, stick to Method one of the beginning. I want to start using it and really, really understanding it. Then you can continue in method to and method three. If you want. It's let's start off with the first method I've called its simple index of match. It's actually like an index match match, just like what we did in the previous example, because we want to look up this agent in the Rose and then we want to look up this actual quantity in the columns here, so we need to go down and across. But the difference between this and the previous example is that we need to have this unique cantor, which we don't have in this case on one simple way around This is to create that unique header by just saying this cell on this cell is combining them together. Because, remember, match was a one way street. It can't look up something like this, but he has to look up something. That's just one thing. And we can create that by using this formula. Now we can write our index match match. What do we need to index? What is our answer? Could be any of these. It could be a quantity, a revenue, a customer, any of these. Because, remember, I want to pull this formula down and across. It should apply to all of these on the answer could be anywhere here. So let's think about the fixing. I wouldn't fix that. How many rows do I need to move down? That depends on where my agent is sitting. It's I need a match here. Find this agent. We're going to fix that in here. Fix that, and I want a perfect match, not a column. Well, I do have a unique header. Now. I just need to make my look up value also unique, and that's basically let me start with a match. That's the combination off this and this. So let's think about the fixing. I'm planning to pull my formula down so they need to fix the 46 and this case quantity in planning to move it across. I need to fix the M. That's what I'm matching. Where that's easy. Right now, it's here when you fix that and a perfect match. Six Agent be actual quantity at six. So let's see if we did our fixing correctly. That's 300 50 customer Be 73 30. That looks good. Because if that's the simple way of writing your index and match formula in case you don't have unique headers, you just create this unique key what you can, then do him. If you don't want to bother anyone, you can't group this and hide it out of you. Okay, let's just check that with another agents. We're gonna check agent age. That's nine 333. That's fine. So now we're gonna move on to Method number two, and with this method, we're going to skip this step here, and the way we're going to skip it is Regan, add an additional index function inside this last match function. I'm gonna show you how that's gonna work. Let me just copy this formula down here on and bring down our cell references. The rest are fine. Okay, This part is fine, right? We agree. We don't need to touch that. It's the last part. That is a problem for us because it's not unique. So we know that we're looking up this combination. Now, instead of having this additional, you need a key. We're gonna use an index function to give us actual quantity, actual revenue, actual price in separate cells that is going to be written inside this array. They're not going to be visible cells. How that's gonna work is we're gonna index this and then we're gonna add an And so look on what I'm not doing isn't I'm not indexing this, but that's not what I need to do, because that's not gonna work for match. Remember, One way streets I'm indexing this on. And this and what this formula does is that it goes actual quantity as position one. It's memories in their extra revenue position to actual price position three. So it kind of makes creates a one way street off this combination in memory here and then I need to specify the row number. But I wanted to do it toe all Rose. I mean, it's it's not applicable here. So either I can put a zero. I can just put the comma and leave it out. And for the column as well. I can put a zero meaning just do it for all the columns and close off that index. Now, this part belongs somebody match function from before. So all I did was to replace that range here, this help her range that we groups before with this index formula, the press enter and they get the same result. So I'm planning to copy this down. I have to fix you're fixing is one of my weaknesses. Always. I realized too late. Okay. And that works fine is well, so now we're gonna move on to method number three, and that's using an array C S E. If you've never used this, C S. E. This control shift enter, it's going to be difficult for you to digest this formula. Basically, in excel, you're able to turn any formula into an array formula. If you press control shift, enter at the end instead of just pressing Enter. What that means is that it does exactly what our index function did here, but without having to write index so you can turn any formula into an array once you do control, shift, enter. And it actually does this thing that Index did with saying this on this as position one in memory, this in this kind of dessert looping on these cells. So I'm gonna show this to you, but I'm gonna warn you. So don't get discouraged when you see this formula. If you don't know it, just continue with the next lectures. Used the simple method Whenever you come across the's on once you're ready to digest thes than comfort is again and refer to this material. Let me show this to you in action. I'm going to copy this here gain Let's update our cell references. And now, instead of typing the index here so you can take this whole thing away. I don't need the rest of the index either. I just need to say from a match, this is may Look up, Harry. It's this in this but not see, if I press enter, I get value. It doesn't understand what it should do to make this do what just our index formula. Did I press control shift? Hold him down. Enter that It works. Making copy this down and across. Who changed this? Agent K 10 3 20 Everything looks good. As you can see, this is the shortest way of writing this Because I turned this into a nunnery. There you can see. I mean, a race have advantages, but they also have their disadvantages. The main disadvantage I see with a raise is that they make your spreadsheets more difficult for others to understand. So if you would like to give up the spreadsheet to someone to your colleagues or someone who is not familiar with this and you're trying to look at oh, how is this formula calculated? Hi, it's taking this cell. And that sounded the press enter. It's gone. Doesn't work anymore because they have to press control shift enter. The other thing is they can make you especially slow. If you have a lot of data, is something you have to take into account as well. But otherwise they are very clean and lean method of writing this formula. No personally when I designed spreadsheets for other companies and I have to make something like this. I will go with Method one because my end audience can be different. Some can be very advancing, can be advanced in some not so advanced and method. One would be the easiest for any of thes to follow. Then I would say his method to and lastly, it's Method Tree, which is more prone to mistakes. So, as I said before a stick to Method one, if you're not used to days and come back and review these methods once you're ready for them. So now let's move on to our last example of the index and match function. And this is a test that I was doing for a company when I was designing a template for them and they had a table like this. They wanted the user to select a product than to select a type, and the type is any of these inside the table, and they wanted the template to provide the header. I'm going to remove that so we can do it together. So now we have a reverse lookups instead of looking up this way and this way to get this. We're gonna be looking up this and this to get this so you can see D can be in the second category. And it's also in the third Cancer Guerry. And it's also in the fourth category. But depending on which product you're using, if you ever have a case like this, we can also use to index and match function. Let's think how we can do this. What do we need to index? So always Look at your question. It's just find category. Our answer is a category was all we need to index. Ignore the rest. How many rows do we want to move inside here? Nothing. We want to just take account this role and that's it. So I can put another comma. I move on to the next argument column number. So let's start off simple. In this case, I want the second column because my product to and D are in the second column. It's just students, See, we get Category two. Yes, so now our aim is to make the to dynamic. So how can we do that? What function can be used here? The two is a position right so we actually need the match function because match gives us a position instead of the two. I'm gonna typing match. What do we need to match? Well, I do need to match the d here. I'm going to start with that. Why do we need to match it where it can be anywhere in here? Right. But remember, match was out one way street, right? So I'm going to start over not making this dynamic or semi dynamic, and then we'll make a dynamic. So I need in this case, Did my d is here because my product to is here. So I'm gonna match this one. Andi, I need a perfect match to still get category to, which is good. So now I'm semi dynamic, not fully dynamic because I fix this. I've cheated and I've said I know it's here because that's the combination. So now instead of this, I'm gonna use a dynamic formula. What's the formula I can use for ranges or a raise? It's the index function. What I can index is the whole thing. Because ultimately I want either this one or this one or the last one, right? So I'm going to start off with indexing the whole thing. Then comes my room number. That depends on my products so I can match my product from this list. Then comes my column, and in this case I wanted to include all the columns. I can either leave it out or put zero in there. Close the bracket category two. Because what happens with this formula is that I index this right. But because I moved down to, I'm actually ending up to only index this, and that works perfectly well to feed my match function with just needs a one way formula. So let's just have a quick look at the value formula in this case. Could that helps here? So we're matching D. We're indexing this whole region B 77 to G 79 and now we're matching product to, So that should give us too right. It's the second positions that gives us two, and our index is still here, and I see it says Match D in B 78 B 78 to G 78. So it changed that to just a one way street, which the match likes and can work with. That's another way you can use the index that match function. This is one off my favorite formulas. Really? Really. Understanding dysfunction is what separates two gurus from the norms. And you're not going to be a guru if you don't practice. So now, Mr Return to practice. But aside from this exercise, I would like to see you take this a step further and actually try to practically see where you can use the index of much function in your existing falls and then actually use it.
8. Formula 6 - The Most Useful Text Functions: text formulas can save a great deal of time cleaning up your data and getting them in the format that you need. But sometimes you need to menu police or clean up the descriptions of your products, your customers or your accounts. Or you need to cut out substitutes or replace parts of your product quotes with another coat. In this lecture, I will show you the most useful text formulas to trust me. They're very easy to use. The key point is that you know they exist and you know how to get them to be dynamic. So whenever you come across a task where you need to manipulate parts of your text, you're going to say, Oh, I know there is a formula I can use for this and you don't have to do it manually. This is the list of what I believe to be the most useful text functions. I'll take you through each one in the demo. We're going to start off with the upper formula. So let's say we have our list of customer names here that we extract room from our system and they're kind of mixed up summer lower case. Some are properly returned and summer mix of upper and lower case. What the upper formula does is you just typing up her open bracket and text means highlight the cell that has your text or you can actually input text like this in there. But we want to be Dana makes, So we're going to highlight that cell close bracket. You get everything in uppercase Lou. Where does the opposite? It puts everything in lower case and prop her puts things in a proper format. What is the proper format? It's like this. So the first letter is capital. The rest is lower case. That's the proper formula. So for text like this one, that's all mixed up. He looks much better in this form. No, forget length. It's the Len function. What the lend function does is that it provides you with the number of characters in your text. So just type in Len Reference, the cell presenter on. In this case, I have 13 characters in this cell. The lend function you might right now think well, what can they use it for everyone. Use is if you have a specific template that you want people to feel and they shouldn't go over a certain number of characters. You can use this function to track how many characters Aaron put in the cell. There are other useful uses to this which help to make your other formulas that we're going to learn here more dynamic. So we're gonna come back toe length a few times in this exercise. Now, in this example, we have a bunch of customer names that have been extracted, Let's say, from a system, and they come with either trailing spaces. Look at this one. Or would spaces before or with too many spaces in between the characters. I'm going to get length before this because don't forget, a space is considered a character. Now, if I trim what is trim do trim takes away these additional, this trailing spaces. So I just trimmed these ones. Now I'm going to get the length after I trimmed just to show you that it did take away thes additional characters. So you see, here I had 17 because I had these trailing spaces, and here I have 13 because I trimmed it. It took them out, and in this case it took away this extra space that I had between Toby and Carter. I remember when I gave a training some time ago. Some people in this training laughed at this trim function when they laughed because it sounds kind of funny, but they also laughing that they thought it's completely useless, I guess. What? They ended up using this in pretty much all their files. Why? Because they told me they've been trying to use the re look up function. Remember the one we learned as our fourth function? They were trying to use this, but it did it work on their data and they were extracting the data from from a SAPI. And he said, It just doesn't work in that datum. We don't know why. Well, guess what they needed to trim. They're download because when they extracted in their case, their product codes, it came with trailing spaces. And when they were creating their report, this one didn't have the same trailing spaces, so it couldn't match it. So they had to trim these first and then do their look up so trim can actually be very useful function. Now moving on to the left, right and mid functions, they can be quite useful when you're manipulating your data, your descriptions and you just want a piece of your text from a larger text. So let's start with the left function I taping left, then detects to Texas basically, the cell you have your text in. And the last argument is how Maney characters do you want to take out off this? So let's say in this case I want the first names I want. Toby, How Maney characters is that four so I can type in four. That's what left does. So if I would type in Lake eight see, it's Toby Jen. What is made do mid does a similar thing, except that we have a start number and the number of characters that we want. So I would select this text. Then I can say, from where do you want to start reading? Is it the first position? Isn't this one here where so in this case, I want to start reading. No, from 45 is empty to sixth. I want to start reading on the sixth, And how many characters do I want to read for? So basically starts reading here and it's 1234 Let's shake that Jane last theme. I can use the right function again in her late May text. And how many characters do I want to read from this site? If I just wanted Carter six case? If I expand that and do eight You see, I get the e in there and then Carter. So I want six que? No. Obviously you're thinking, Well, where am I going to bother to look through each name and put a four and then the next name is maybe longer and I need to put like, a six or five. I'm not going to do that manually. No. And that's where the find and the search functions come in, as well as our length function, which can make this whole thing dynamic. What is the find function? Do? Let's try it out. I'm going to find What do I want to find? So the text that I want to find in this case I'm looking for tea, so I'm going to put t in quotation marks. If I had tea in a separate cell, I can reference that as well within text. Which text is this one and start number again? You can say, Where should it start reading from the first? Or you can start reading from the fifth position, six position and so on. If you just wanted to read from the beginning, you can leave that argument empty. I get 14. So it says that t is the 14th position. So that's look, I have a small t there. 123456789 10 11 12 13 14. And notice that it didn't find the capital T because I was looking for the small tea. So now let's just look at what searched us. Before I explain the difference between the two, we're going to do the same thing. You see, the arguments are the same as find. So I'm gonna search for tea. I'm going to search within this text on my starting position. Actually, I'm going to leave that empty because I wanted to start from the beginning. It gives back a one, although you see that here and here it's the same formula. I'm just using a different function. But here I get a one, and here I get a 14. This highlights one of the differences between find and search search is not case sensitive , so search doesn't care if you're looking for a small tea or a capital T or this is a small tea or capital T. It finds the position where it occurs first, whereas find is case sensitive. So if I was looking for capital T, I find it as the first position. And here, if I was looking for a capital T, it doesn't care. It doesn't matter. The Capital T or a small tea the first time it occurs is at the beginning. That's one of the differences between find and search, and the other difference between them is that search allows for wild cards, whereas find doesn't in this case, let's say I was looking for T E. R. Okay, so let's say search TR. It's the 14th position here, and I was finding also TR 14th position. But now let's imagine that I want to use a wild card instead of the E. So I'm gonna use the question mark on the question work. It's like a placeholder for any text that can occur between T and R. That gives me back still 14. If I use the wild card here, it doesn't work to recap. The difference is that search doesn't care if it's capital or lower case. It's not case sensitive, and search allows for wild cards. So the find function is actually more restricted than the search function. And because of that, I generally used the search function on this. Of course, I really need to pay attention to Capitol and Lower Case. So now moving on, how can be used this function together with this function to get our dynamic first name last name calculations done for us.
9. Formula 6 - Advanced TEXT functions: to get the first name in a dynamic manner, we're gonna use the left function. That's our text. And now we're going to use the search function to get how many care actors we want to get stripped up from the left hand side on. What we're searching for is the space within this text. It should start to read from the beginnings. I can leave that argument out and I get John now. The results of my search here is five because space is the fifth connector in this name. So it must have stripped out the space as well with this. So you fight testes in a get the length of this. You see, it's five and No. Four. So what I would need to do is either to make correction to this and do a minus one, or the way I prefer to do this in this case is to trim this and get rid off this extra space. So I'm basically trimming the results off this formula, and you can see it's for now. Also, to get the first letter to show us capital, I can add the proper function to this. Let's check this looks good. Now the way that I work with text functions that get big or actually with any excel formula that ends up to get bigger and bigger, I always start with the inside first. So with the most basic thing first and then I start to make it more dynamic, and I add on other functions to this. So for the last name, we need the right function. That's our text. I know again, how many collectors should be strip out from the right hand side. In this Anderson case, we need eight. But we need to get the AIDS dynamic. And to get that, we just have to think a little bit. How did we get age in this case and then four? In this case, the way we can do that is to get the length of the entire thing and then deduct the length of the first name and the space. So basically, if we find the location of that space like we did here, we can deduct that foam the length of the entire name. I'm going to do this separately so that you can see the results of this formula before a copy it in so if they get their length of the whole thing, it's 13. Now, if I deduct that position of that space, which is five, I should get eight. So I'm going to look for that space within this text start number. You can leave that empty. You can put a one that's eight. And now I'm going to just copy this and paste this instead of my eight. Notice that I don't need to do a minus one correction or add a trim function here because I start counting after the space. So now let's add the proper function to this a swell that works quite well. What happens if instead of John Anderson, I have John Andrew Anderson. So they have a middle name. Now that's all going to be taken as last name because, remember, we look for the first space, and the rest of it is the last name. So that takes us to our next example. First name is easy. It's basically identical formula that we have here, so I'm just going to copy and paste this here now for the middle name, their different ways of writing this I'm gonna use the mid function in this case. So we get to practice with that a bit. The text that we need is here. The start number. Okay, I'm gonna warn you, this is just going to get a bit complex. If you are very new to these text functions, you better not be watching this at night or if you're tired. So maybe you pause this, watch this in the morning or get a coffee right now just to warn you. So start number in this case, start number would be here where the J is. So that would be our sixth position. Is that what we ultimately want is six. And in this case for Jane issue, go over four. Let's just test that before, see if it works. It's fine. Let's get the length to make sure we're not adding any spaces. In this case, it looks good. So now our Amy's to replace the six and a four with a dynamic formula. So let's start with the start number. The position to start reading the middle name is after the first name is over, and I already have the first name here. So the length of my first name is what I need in this case. Is that enough? If I keep that that way, let's just try it. No, because the length is four and the mid function starts counting with the fourth included. So it's the widest space to J and the A. So I don't want that. I want two counts the first name. But then it should move to after because I have a space and it shouldn't count the last letter either, so I can add a plus two. That looks good. Now we need to replace the four. So let's think about how we can get the four in there in a dynamic manner. I'm going to do this in a separate cell, and then I'm going to copy it in here. So how do we get four if I get the position of the second space in here? So that would be 10. And then I deduct the first name the length of the first name. So that's four. And then the two spaces that should give me four. So let's just check that I'm going to do it separately. So let's search for the second space. How did I do that? The way I can do that is to say, start reading after you finish reading this on the space. Because remember, I have the start number that I have been leaving out until now because I always wanted my search to start from the beginning. Now I don't. I want the search to start from here from the J. And I can do that by saying start reading. After you get to the length of this, plus two to them, find the next space 10. Good. That's the position of this space. Now we want to say, deduct the amount of the first names to deduct the four. That six. And now I have two spaces here. One here, one here. So did actors as well, that's for. And this is what I can copy instead of this four. Here I get Jane. So now watch what happens when a copy this down Errors. Why? Because I don't have a second space here. So what can I do to handle this error? I hope he said, if error in case this results in an error, which means I don't have a second space. I want to get nothing because this person does not have a middle name. We're sat well here. Let's check by having John Andrew Anderson, that looks good as well. So now for the last name, I'm gonna use the right function display text. Now, I can't look for the first space. I need to look for the second space and then strip out everything that comes after that. So one way I can do this is to just use the information I already got from the first theme and middle name to get my last thing. Basically, if I say give me the entire length of this, deduct the length of the first name. I'm deduct the length off the middle name. I still have two spaces here, I could say minus two. But then if you look down here, I don't have to spaces. Let's just leave it with minus two. I'm going to show you what happens this when you work. Fine, it's Carter. But when I copy this down, you see got stripped out of the A and of the M here. And the reason is these guys don't have to spaces just have one. So if we look at these, they always always have at least one space. So what I can do is make a minus one correction to this so that these ones work fine. And to get this one working correctly is to add the trim function to the whole formula, because if it has a space, it trims it. If it doesn't have it, it leaves it alone. That's how you can handle cases where you have middle names for some people and not for others. But as I said before, there different ways of coming to dissolution, and it'll be really glad if you share your way with the others as well. So moving on to our last bit, that's what I would introduce you to the substitute and the replace function. They might come handy depending on your task. Sometimes you don't want to use the Excel in a search and replace if I do like control h, I get the find and replace. You don't actually want it hard. Quote that in your in your foul, but you might want it in a dynamic way to track it separately in formulas. That's when you can use the substitute function. First, you have to select which what is the cell that you're looking to substitute in. Then what's the old text that you have in this case? I want a substitute 80 with D for these codes, my old Texas 80. Either you type it in in quotation marks or you can reference a cell. And the new text that I want is D How many instances I want. I'm going to leave that out. My 80 code has turned into D ive one, Miss, have a look at the rest. This one hasn't been touched. This one hasn't been touched on Lee, where I have 80 I get d. That's the substitute function. Replace is slightly different because you're not looking for the actual text that you're replacing. Instead, you're looking for a position inside this text to replace, so to demonstrate, gonna start off would replace again. You referenced the self first, and now you need to select the start number. So what is the position that you want to start replacing? In this case, it's one. And how many characters do you want to replace? So if I want to replace the 1st 2 characters with D, I would say to and what's the text that I want to replace it with his D If I copy this down , notice that I'm not searching for 80 and replacing. I'm just looking for the position and saying, Start reading at one. Replaced the 1st 2 characters with D and so it does it for all. Given this, you have some more flexibilities. So if I wanted to replace the last two digits with 10 for these codes, I could do that start number. What would be my start number in this case, if all the codes have the same length? When I can fix it, I can say in total, What is this? It's 1234567 So I want to replace the last two. And that's six and seven so I can see. Start at six. How many characters shouldn't read, too? What should be the new text? Zero 10 Yes, so it turns my 80 25 0 92 It takes away the 92 and puts 10 in there. I can also say replace the last two digits with two zeros. All I would have to do is to do a replace again school text, start number is the same. Six. The number of characters to to read to replace is, too. And now we two zeros. I have to put it in quotation marks to say it's text when they put into zeros. So these two functions, substitutes and replace can coming handy, especially if you're extracting data from the system. And you might need to change them a bit and uploaded into another system. Now, with this substitute function, now that we learned this, we can also use it here for the last name. How can we do that? I can be used substitute here. We can use it quite smartly by saying from this full name, we aren't going to substitute the first name on the middle name if they have one with nothing, and that means that we're left with the last name. What is my old text? My old takes is basically my first name and my middle name. That's what I'm replacing with nothing. And that, basically is this. And but remember, there's a space between them, so I have to account for space. Andi. Middle name. It's that gives me Toby Space Jane. What am I replacing this with with nothing, So let's just see what that does. I get Carter with a space in front before I trim this. Let's just make sure that it works for the rest. Anderson. It works fine. This one, it works fine. It's all I'm going to do is at a trim function before, and he will work fine for all of them. So that's how you can also use the substitute function. Now. This is not an exhaustive list of text functions that are available. It's just what I find to be the most useful text functions in Excel. So whenever you need to manipulate text codes descriptions, consult this lecture because you will probably find your solution to be a mixture of thes techniques here. So, no, it's your turn to complete an exercise.
10. Formula 7 - Excel's Top Date Functions: state functions are great when you're creating a timeline for your project, where you're putting together a reporting timeline that you want to send out now. What I've often see people do is that they use a calendar to do this. Don't do this because you can make mistakes. Instead, use Excel state functions. There's a bunch of really useful ones. The first step is to know that they exist, and the second step is to learn how to use them. Here's a list of the date functions I think are beneficial, and I personally used to save time as a first step. Let's start simple. Assume that you have the year, month and day each in a separate column, and you want to get the date in one column. All you have to do is to use the date function. You just have to select which column has your year in it? That's this one. Then the month. Then today close the bracket. Now you can see here that I have a custom formatting. Let's go back to the formatting with our shortcuts, Control one. And here you can see the special formatting that I've used in this case, you can find some general date formatting in here is well, But if you don't find what you're looking for, you can create your own. All you need to know is that for day you use D for month is m for year is why and then the combination and the way you use it defines whether you see the number or you see the text. For example, if I would type d d d and then another d I see the full text comma space, then I want to see the actual date and then I want the month that was Mm. And if I type in another M m a r, I want the full march, I have to type in the M four times. Then I can put the year for the full year. You are quite flexible on how you want to format your dates if you use the custom formatting. Now, if this was formatted as general, you see a number and this number is basically the number of days since January 1900. Because Excel starts computing days from the year 1900 you can see that if I change this to 1900 this 1st 1st this becomes a one. Let's put these back. And now in a similar manner, I can do what I just did above the other way around. So assume that you have the date. It won't call, Um, and you actually need to split it into three separate columns. What you can do is to use these functions. So for a year, just use year andan. Your reference. The cell that has your data, Newt for months, the same thing. The last function for the day is day, and then you reference your date. Next is the week they function. This is a great formula to find out the day off the week. So if you want to know was the 21st of March and Monday or Tuesday or Thursday, you can use the week they function. You referenced the cell that has your dated it. Their return type is important because that's what defines if your week starts on a Sunday on a Monday, Tuesday and so on by default. Excel considers your weeks to start from Sunday. This to, in this case to considering the movie stars from the Sunday means that this is a Monday. If I leave this argument empty, it's the same thing because Excel defaults two weeks starting on a Sunday, which is the one I could change this. And if I put it to what would this number be now? The one because my week stars from a Monday and this means this is a Monday. Now I can get confusing if you always have to think, does the to mean it's a Monday or a Sunday because was this a one or a two? What you can do is to former this cell to show you the day. But you have to be very careful here because if you are going to do that, you need to use Excel's default one or basically nothing, which defaults to one. We go back to our format cells, go to custom. We're going to change this to either treaties or forties, and then we actually see the text that's associated to the day of the week. But as I said before, to get these correctly, you have to be using Excel's default one, because if I do use a to it doesn't work. It thinks the 21st is a Sunday and it's not. It's a Monday. Just keep that in mind if you are changing the formatting of yourselves to reflect the text for the day of the week. Next in line the week number. Now for dysfunction, you don't need to look in the calendar to see which week number is associated to this state . You can use excels week number function. You just referenced the cell with the Dayton. It just like we did above. You need to define how your week stars gave my default. One is a Sunday to is a Monday. I'm just going to leave that empty, so that reverts it. A default. May Week stars from a Sunday, the 21st of March 2016. Let's have a look at the calendar that's March 20 1st is the 13th week, but not in my calendar. I have the week starting from Monday, so you have to be quite careful when you're using this. So if I would change this no to a to obviously this will still be 13. But if my dates changes to the 20th a week number changes to 12. That becomes Sunday, and that's in the 12th week. If you're week starts on Monday. So whenever you use the week number function, always take this into account and also be very transparent on how you're weak. Numbers are starting, especially if you're using this to define deadlines. Or you're having discussions with other colleagues who might assume that the week number starts from indifferent day of the week. But moving on to the workday function. This is a great function if you're creating a project plan or you're creating a reporting timeline or any template that has dates and the number of working days people need or you need to complete this task. So don't rely on the calendar. Because if you make mistakes, excel is the safer way. This case, we have a start date. We know the number of working days. We need to complete this task. We want to know what date we need to finish this task by, except has a great function called work day function. Workday by default excludes weekends, so Saturdays and Sundays are excluded. And if you want, you can also exclude any public holidays that are defined by you in a separate range. This is my start day. I have three working days for this task. Let's just forget about holy days right now. Let's assume there no holidays here. I need to finish this task by Tuesday the fifth. And how does that work? Because the weekend is automatically excluded. Now let me show you how you can also exclude holidays. So we have worked. Day this. That's the number of days. And now here this is the optional argument for the Holy Days. All you have to do is type or copy and paste your holidays from some template in excel somewhere in a specific rage and just highlight it. That means I'll end up on Thursday, the seventh of January. We can double check this here. I just want this to give us a good overview Thursday. We start off here three days, but holiday should be excluded. Means we can't take this into account. He's also not so that's 12 That's a holy day three. So we end up actually, one week later from the Thursday to a Thursday. It's three working days. In the last case, I just wanted to show you that you can also do it the other way round. So you can also go back days. You have your end date and your days can be minus three. So go back three days and you have these holy days You end up on Thursday 31st. He's a works both ways. Another useful function is the network day function and that worst kind of similar to this one except that you have a start date on an end date. They're both dates, and you want to calculate the number of working days you have available to you between these dates. Network days, start date. That's our start date. End date is this one. And though it gain, holidays are optional. So let me just believe that argument out. I get four. So between this Thursday and Tuesday, I have four working days. What you need to take into account when you use dysfunction is that it counts the start date and the end date as well. So here's Thursday race. So that's one two because I haven't excluded holy days yet 34 Now let's exclude the Holy Days as well. So we have network days start and these air our holidays Friday is excluded. That's one, 23 and again here. I want to show you that you can go backwards. So here I have a date in 2016. I'm going back to 2015 and I want to see how many days I have to go back. So that's my start date a gain, my end date, my holidays. You can see that these air very simple formulas. But they came really, really not only save you time but also save mistakes. You might have noticed that when I type these formulas, I get a second option. So when I was typing in the workday function, you sell Worst eight International. This function was added since Excel 2010 and the difference between this and the normal workday function is that you can control your weekends so you can define if your weekend is a normal Saturday Sunday weekend or it's something else and it's Friday only. Let's take Friday. Only it early assumes that Friday is a weekend. But Saturday and Sunday are working days, so this was introduced since Excel 2010. To be careful if you're using this function, but distributing your file to people who have older versions of Excel the same thing is available for network days. You have network Days International, where you can define the weekend. A few weekends are always going to be Saturday Sunday. Just use the standard network day function and the work they function now just to combine these, maybe for a more realistic case. So let's say you have a start days and an end date. You have companies in different countries, in this case in Austria and Thailand, and you want to calculate how many working days each person in these countries has available to them to do this task. And you have the holy days listed somewhere in Excel. We want to write the formula in a dynamic way. As a first step, the formula we need to calculate are work days is network days. Start date we knew and date. We know now the holy days. So obviously I can write a different formula for Austria and a different formula for Thailand. I could also use the functions. A. If this is Austria used this range otherwise, use this range. But let's just assume that I might add on a lot of countries to this, I wouldn't use another function, and I'm going to suggest What do you think the index function? Why? Because I want to show you another use off the index function here. I want to say used this range. If it's Austria used this range if it's Thailand. So what I want to show you is the other use off the index function using the area number. What you can do here is to select different areas separating them with the Kama that you just have to be careful because the movement to put the comma here excel things. You're moving on to your roll number, but I don't want to move on to my room number. I actually want to say this is what I'm indexing. They belong to the same argument. So I have to work with my brackets here and puts the bracket after a finished selecting the last range. And then when they put the comma, you see, then it moves on to the road number. How many rows do I want to move? Well, I don't want to move any rules. I just wanted to take into account every single dates that I have in each row so you can skip that argument. The same for the column. Now comes the area number. An area number by default is this is a one. So that's my first area. That's my second area because I selected its second. If I just type it. No, manually, I would put a one here. I'm writing for Austria. Let's think about our brackets. I need to close this one. Who is it again? I get four working days. Let's check that from this Thursday to this Thursday. And remember, network day counts start and end dates as well. So that's the one. It's a holiday in Austria. That's the weekends. That's 123 holiday Austria four four days. If I put in area number two, which means taking out Thailand into account, I get five, because here I have the common holiday between Austrian Thailand. But this one is only a holiday in Austria and not in Thailand. So that's one, 2345 Obviously, I want this to be dynamic, depending on whether this is Austria or Thailand, which function returns a number or position the match function. Match this. Where do I want to match it here? That it fixed that Because I want to pull across. I'm going to fix these actually as well. So they don't shift in these as well. I'm gonna match this in this range, and I want to have an exact match. What this would return is it looks this up here and this is position one side returns the one which automatically means area number one. If it's Thailand, it would return to which means area number two. So that should work. Well, if I've done my fixing correctly works great. So in this example, you can see how we can use always different functions with each other to get dynamic results. Remember to consult these functions whenever you are creating a project plan, a task list or a reporting timetable because they don't just save time, but they save you from making mistakes. I was returned to practice
11. Formula 8 - OFFSET for Dynamic Calculations: offset is an interesting formula, and it's one that can do so much more than meets the eye. The offset is a way of giving Excel an address to go to. So you start off by telling it how Maney rose to move and then how maney columns to move to get to its destination. Now this destination can be a single house, like a single cell. It can be the streets, like many cells in one drawer, one column. But it can also be like a town like an area. Arrange in excel. Now, you might think, What on earth can this be useful for? Why would I ever need to use an address in my Excel files in this example? I have months here and sales revenue here, and I want to get the average off the last six months. So obviously one way of doing this is to use the average function and then highlight this Blake this. But now the problem is that this is a dynamic report. So every month new data comes in for August, I put into data here to get this average of last six months to work properly. I have to drag this range down manually, and that's something I want to avoid. And the offset function is what helps me avoid this. To show you how we can do this, I'm gonna teach you offset on its own. As a first step on as a second step, we're gonna integrated into the average function. The syntax of the offset function is that you always need a starting point a reference. It's like before you start your walk. Where you going to start? You can pick any cell, but it should be close to the range that you want to end up in. So I'm going to pick this cell and then how Maney Rose, do I want to move down? So let's say I wouldn't move down one. How many columns do I want to move? I'm gonna put zero here because I want to stay in this column. And the last two arguments are the height and the with a 11 means one cell. These last two. They can never be 00 At the minimum, they can be a 11 If I wanted, like thes three, I would put 31 on the stick. Would 11 1st we get 100 so we start here. Go down One Don't move. Columns, Give us one cell. It's 100. If I would move down three. I get 1 30 if I move columns. If I actually put a minus one. So meaning from here. Go. Here, I get my date back. It just I would just have to change the formatting. If I do a plus one, I get a zero. Because where am I? I'm here. Let's put it to see I'm here. That's basically what offset does. If I did want a range, I'm gonna put this Call him back to zero. And instead of having a one for height, I'm going to put three for heights. I get value because what it does is that I'm starting here. Moved down 3123 and then give me thes three cells so it can't actually put thes three cells in one cell. Now, whenever you use offset in a way that these last two arguments are ranges, they're not just returning one cell, but a range of cells. You need to wrap your offset formula in a formula they can handle ranges. What would that formula B, for example, The average formula, what is some formula or account formula? Anything they can handle ranges. So in this case, we're gonna use the average formula 108. So that would be the average of these three. Now, we're going to expand on this formula to get the last six months. The different ways of writing this one way is that you can first see what's the last cell in here by using the count function and then come down to the last cell and then as your height, you kind of go back and highlight the minus six or six rows before this, or you basically find your last cell and then you go back to the six to row and your height is them plus six. Don't get confused by this. I'm just going to show you as a first step. The simple one is the one I prefer and then the other way as well. Later on, you can also play around with it and see how they work as the first method of writing this . Now I start here right. I need to know how much should I move down I can do this by counting the number off filled cells that I have here I can use here The count's function. When account a function count a basically counts. Even if its text the count alone only counts. If these are values in this case, it doesn't matter, count or count A and then they highlight this. And the important thing is that you highlight more than what you have, because in the future I will have data here. So I need to take them into account as well. The result of this count a function is going to be seven. It's gonna tell me Move down! Seven, which is 1234567 I end up here. How many columns to move? Nothing. I wanna stay in this column and what's my height? That's where minus six comes in because I want the average of the last six months minus six will be this from here to here, and one is that's me with 113. Let's check that 113. So now when they add a new months, two days it's 1 to 7. Went to seven May range updates automatically can also check this by going to formulas. Evaluate formula. I started here I go down eight. Don't move. Any columns minus 61 So the final range that it's taking the average off is be six to be 11 . Which is this to this? The other way of writing this. Like I mentioned before, just show it to you. Is average offset? Now we can start here. In this case, the number of rows I want to go down is I'm gonna do account a cane or let's do account. Now I like this and I do a minus six. So basically the results of the count formula in this case now is going to be eight. They added another month minus six. It's too. It only moves down to rose 12 for my columns. It zero I don't want to move any columns. And for my height, I don't do a minus six, but instead a past six. I'm a with is one. I get that which is the last six months. Two different ways of writing this You come to the same conclusion now What if your data was organized in this way instead? So we wonder Average of the last six months, but we have them in rows. We do the same thing. I start off with the average function, then the offset. And my reference is here. Let's say I want to start off here. How many rows do I want to move? Nothing. So that zero. How many columns do I want to move again? That depends on how many columns I have numbers in I'm gonna use to count function four the count A function. And I'm gonna highlight until where I think I'm gonna have numbers in now for height. It should just be one now, But for with I need from here to go here, I needed to be minus six. Okay, so it was just the other way around. But it does the same thing of getting the average off the last six months. So if I add data here for August, I get 1 to 7. So just be creative and practice this in all directions until you get the hang of it. Now, another good example on this is if you have your months and your sales revenue and you have a report where you always want the average of the next three months based on a selection and say you've included a drop down here where you can select your month from this list and you want to get the average off the next three months, we're going to do the same thing. So we're gonna use the average function together with offset as a reference point that can be fixed. We can always start off from here. And then how many rows do we want to go down? Well, in this case, we want to move down until we get to 4 14 which is here. We want to move down this much. Which function can a use that gives me back a number the match function? Because that gives us the position off 90 in this list and the results would be the fourth position. So I know I need to move down for Rose first. I need to have the look up value. I'm looking this up in here. I want a perfect match. Never forget that. That's how many rows I want to move down. Because the results of this is four 1234 Now for the columns. I don't want to move any columns. I want to stay in this column, so that's going to be zero for the height. That depends on my question. If I wanted to include this month as well, I would put a three and a one from a with 102. So let's check that. That's this one. These three. It's 100 and two. If I didn't want to include this month, I just have to add a plus one to my match so I can say Find this, then go down one extra. That would be the three months after this. So that would be these. This is purely dynamic. So if I would go down in select December 2014 I get to 32 In this case, I'm doing the next three months to three months after days. It would be these three, 232 That's how you can use the average function, the offset function, the match function all together to create this dynamic effect. A word of caution is that offset is a volatile formula, which means it calculates every time you make him move in excel. Not to be honest with you in the fouls that I've used offset in. I've never experienced my fast to become slow because of me using offset. But I also don't overdose on them. So I also recommend that you don't overuse them now. As you can see, offset is a function that can be used inside many formulas to get them to have flexible ranges instead of fixed ones, which makes dysfunction a perfect candidate for its use in dynamic reports on dashboards. Now it's over to you to do an exercise.
12. Formula 9 - FREQUENCY, SMALL & LARGE to Organize and Rank: did you ever need to organize your data into categories and then report on these categories ? So, for example, assume you've been asked to report this. How many people are within these age groups in your company? Now you ask your HR department, you tell them. Please provide me with the age of each person working here, and then they give you this. Now, what do you do? Do you go through the list manually and then assign each to a category and you add them up ? Or do you go about filter your list, or do you use a complex A function? After you watch the sector, you will do none of thes. You're gonna use the frequency function. The frequency formula is often overlooked because it's not a normal formula. It's under very function. This means it has to be typed in a special way. But the good thing about it is that it's an extremely simple formula, and you can save a lot of time. So in this example, we want to find out very quickly how many people we have who fall between 20 to 30 years old, 30 to 40 40 to 50 and from 6200 and below 20 and we're going to use our frequency function to do this. Now, it's very important that you pay attention to the first step on to the last step because those air due to steps that most people miss out on and then the formula doesn't work. So first step is you don't just type your formula in a single cell, but you highlight the area where you want your answers to be your numbers to be. You see how that differs from a normal formula. And then I put in my formula. First is the data rate. This is your role data that you've been given on. We've been given this. Then comes your bins array and been zari is your intervals. You have to type them out like this obviously can highlight this. This is the text to support this. And what this translates to is what I've written here. So if you have 2030 40 just note that 30 means from 20 to 30 and thirties included. All I do know is close the bracket and now is the last step. Pay attention here, don't press enter like you press in a normal formula. Instead, you're gonna press control, hold it down, and then press shift. Hold it down. No press. Enter. There you go. That's an array formula. Now, let's just check if it got it right, So we should have three people that are below on, including 20. So these three Carrie just gets more. So if I would, for example, change this 21 to 18 that's for it at seven. Said this treated as one formula because it has to look at the entire data set and reallocate, and you cannot change parts of this formula, so I can't go in here and type in whatever minus one. As you can see, the frequency function is a very simple formula that gives you the number off elements within your data that fall between specific intervals. But you can use this for salaries, revenue, quantities, anything that's numeric. And don't forget control shift enter. In this lecture, I've also included for many functions that help with ranking and categorizing your data. Here's a list of them here. I have a list of companies and the revenue, and I want to create a report that shows the best the worst third best and faired worst revenue and company for best. You probably know that formula. It's the max function. So, Max, you just have to highlight the range where you want to find the max in. That's this one close bracket, and that's my value. For Company J, the worst is the main function. Again, we do the same thing. That's 1000 Company K, except has some other really useful formulas that you can use in ranking. So if you want the number 1234 best companies or the third worst performing company, you can use these formulas for the third best we would be using the large formula. This function is similar to marks, except that you have the option to decide the numbers. Is it like the third best, the fourth best, and so on. First thing is highlight the range and then put in the number the rank that you want to get . So in this case were the third best. We're gonna put a three very wanted, the second best I'll put it to. And of course, you don't have to type it in. You can also do some references, So if you had 1234 In a separate column you be referencing those cells. That should be my third best. So this is my highest number. And then it's this one. And then it's this one for third worst. It's the small function. You do the same thing. I let your range. Then I put it three. If I want to find the worst, that should be my men. Right? See, that's the same one. If I wanted the second worst, that's 9000. And the third worst takes us to 11,000. Okay, so we're halfway there. But we also want to get the name off the best. The worst. Third, best and third wars. Which function can be used for that? The index that much function? What? Do any to index here? What do I want back a company, right? So I only need to index the companies. How many rows do I want to move down? Well, that depends on murders. Number is sitting now. I can reference the cell where I can write the formula in a formula. First thing is that I want the positions I need to use the much function. And inside the match Instead of saying match this, I can also say match the max of this in. Where should it look again? In here, The much type is zero company J. Okay, so don't forget you can either do celeb references or write the function in the function so I can copy this pace this year. Change this to men company K. Change this formula here to what was it large? But now I have to define which 13 And here it was small three. That's my report. But what's the problem here? Well, what happens if two companies have identical revenue? That's a company G is 2500. What if company A also had that May 3rd best is gonna be company A. But if I change this to to my second best would also be company. A company G is not going to pop up in my report at all because the index a match is going to stop here is going to see this. It's gonna match company A. It's going to leave the formula to avoid this. What you can do is you can introduce a helper column in your data table. Let's say we call it help for revenue. You manipulate the numbers by very, very small amount to make them unique. They're different ways of manipulating them. The way I normally do is I add, and I use the role function. The role function basically returns the rule number, but I get is this plus role? A 47 basically returns 47. It's the row number off a 47 which is 47 some adding 47 to this number. This is what the role function does. I don't want to add this. I'm going to leave that out. If you leave it empty like this, it basically takes the row. Were you writing or formula in? If I would leave that You see it adds 49 to this. So I used this and then you say, I don't want to ask such a huge number two days. I divide this by like a 1,000,000 and I end up adding a very small number to this. And the reason I use role is because when I drag this down, obviously my role changes because here it's 50. Here's 51 some always adding a different small number to each one of thes, which makes them unique. We look at this one now, this has a six year, whereas this one has a five. Based on this, I do the ranking. So going back to our formula here, let's get rid of this. What you don't want to do is to do this. This looks fine. But some colleague or someone might come across this report and they go like this. And they were like, What? Why do we have this stuff who added these numbers? Why are our numbers not exactly matching to the ones in the system who is manipulating this and so on? So to avoid any discussions that can arise from this and any credibility that might get lost Because of this, I don't suggest that you do this. Instead, you find the number based Sundays, but then show the original number. And how can we do that? Which formula can be used for this? They're different ways of doing this so you can use offset. A swell. You are. You can use index. And since we have been using index now, let's just stick to that. The range that I need to index is where's my answer here. That's what I want to show and how many rows I need to move down to show that is based on this. So again we start with match. We're looking for this. So we're looking for the third larger third best in this case. Where are we looking it up here. You want an exact match? Because so if I opened this up, it's all good. It's our original number. No. In the same way, we will do the small function, not comes Air index a match. What do I need here? A company right on Lee Need to do is highlight this area. No, again. Are much is based on not this number anymore. As I can't highlight the cell in this case, I need to again find the third based on my helper Revenue column in this. And I want an exact match for the columns. I can skip that. I get company A as now. My official third, is it really my third? That's my first No, Only second is this one because this number is slightly bigger than this number. Now comes the test. I want to look for the second best that number is the same. But now I should get Company G. And they do so. It works great. I know. I'm just going to copy this. Change this to small change this to three. That's my company. E. Let's put these back as well. And that's how you can effectively use the large and small functions to rank your data. Even if you have duplicates, no, to return to do an exercise.
13. Formula 10 - SUMPRODUCT For the Gurus: keep in mind the sequence of my top 10 is not important. What is important is that you know off these functions and you know how to use them when the time comes. Having said that, I've kind of kept the best for last. I'm not sure if some product was actually designed to be this great or if he just happened to become like this. You've gotta watch this lecture to find out. The classical use of some product is to do a sum of multiplication. But to give you an example, you have here price and quantity to calculate your total revenue. You're gonna multiply price by quantity and then you add up the values in the Revenue Column. Now you have a calculated column and a total for this calculated column, which is your total revenue with some product. In the other hand, he just have one formula that does it all for you. You start by typing in some product. Then you just highlight your price column Atacama to move onto the next argument, followed by your quantity close bracket and you have your results. The good thing about some product is that it can work in a raise of values, and it doesn't have to be entered as an array function. So there's no need to press this control shift enter like we did when we learned the frequency function is kind of like a very elegant every function. What is actually doing is it's taking the first cell, multiplying it by the 2nd 1 doing the same thing for the next role and adding the result. So you basically multiplies and then sums. That's why it's important that your ranges have the same size. Let's take a more detailed look at this function. So the way I wrote it here, I use the comma, right? That's by default. It's like this now, the way I generally right, My some product formulas is like this, and I'm gonna explain to you why, by going through this example, this was a simple case. If you've ever used some product, you've probably used it in this form with some product. What some people don't know is that you can also make exceptions so I could get the sum of a say in this case quantity for only product A and you can use the same formula and now because I'm making an exception. I'm only looking for products. Better product A. I have to highlight this. Then they see equals this. This is something you have to be so careful with. Is the brackets here? So whenever you use this equal sign in some product, make sure your argument is separately inside brackets. And then let's say I used a normal argument with this comma case. I want to move on to the next Array. I want quantity. So all I have to do is highlight this and you see, make sure you're ranges are obviously the same size. So I'm gonna close this in terms. Does it work? No. Let me switch this to this. It works. Why? Because what this does here is is checking if this is product a true or false. So that's the results of of excels. Check is in this case, it's true. True, true false falls True. When you leave it with the Kama, it leaves the true false is text and it can do anything with it. So it's zero when you use the multiply sign and Excel tries to multiply a cell. That's Bolin. So that's true or false. It turns it into one and zeros. So basically, does thes become 101? So it does. One multiplied by 200. That's 200 plus one, multiplied by 8 33 plus one, multiplied by 100 so on. That's why I use the most apply site. Let's just see if our number is correct. So let's just add up wherever we have product. 80 quantities. That's for for three. That works. Fine. If you do want to use the comma, what you can do is you have two new some numeric operator on this cell. What some people do is that they do this. This is basically minus minus to get this. So it's kind of like you're multiplying it by a minus one, reversing it. You can also do like one multiplied by this. As long as you're doing some type of multiplication on this Excel is going to translate a true false toe, one zeros. So in order to just avoid all that, I just change this comma to this. The moving going to calculate a revenue. I do the same thing. I have some product. I want my exception in there again too. Don't forget the bracket. Let's just assume I don't have this. Call him here. I'm going to do price and then I can do a gain two times quantity to double check. That's gonna be these And this one. It's that now what most people don't know is that some product can do so much more than this because of its nature off the way it's designed, it can do what we look up or index a match can do. It can also do what some effects can do and do it even better than some if s can now stay with me in this one, because understanding this function is going to save you from doing a lot off in between calculations and unnecessary workarounds in your existing Excel files. The good thing about some product is that it can also do or conditions. And that's something you can't do with. For example, some if s or count if ISS If you remember, when we covered this function one Murray cover some effects, we could only check for one thing for one criteria in each column. So we could only check if companies A but we couldn't do both we couldn't do and or a or B . We were restricted in each column to one condition. We are not with some product because you can use or conditions here, So let's see how you can do that. In this case, I want to see if it's company A or companies. See, that's my year in my month. Look, I'm doing account. I'm gonna use some product to count. I'm going to show you how. Now I'm saying if company is a or B So again, brackets are very important in this case, this is my company. If it equals to this once, I'm gonna close that. It's my first argument. Now the or argument is a plus here. That's the or this is. And this is or then if this range equals this one close bracket, my whole or condition needs to be in separate brackets. Can you see that the list just person turn to see what happens? I get a number. So that should mean that they're seven instances off Company A and C in here. That's right. I'm actually also doing account, but I'm not done with my argument because I still want to check for year 2014 and months to be January. So right here I'm gonna welcome Bracket Year equals this. Remember, whenever you have the equal sign, put that that part in brackets and my month. So it should be five instances for this combination. Now what? I'm gonna show you, let's check that. But let's check that here. Because what I've done down here is to show you the some product in slow motion what it's doing, whatever formula does, is it turns thes two true false as the first instance. So in our case, we saw, like company A or C 2014 jam thes lines, either ones where everything is true as a second step because we're multiplying, it's turning them into one and zero. So basically goes through this and does one times one time zero that's 00 times. Want insulin? Zero. These are one and these air one. So in total, you can see I have five instances where I get a one when that's how it works in the same manner we have the some for quantities of one of some quantity. But this is still our criteria to make things simple. I'm just gonna copy this formula, paste it here, Okay? Because until here, that's what we need is identical to what we needed for account. But now I just need to bring in my quantity. Call him. That's for 70. Because let's that will check Here it does this time system since and then times this, that basically is one times this one times 91 times 100. And that gives us for 70. That's our 4 70 here. Now I'm gonna move on to revenues. I just want to multiply quantity by price. Let's just assume we don't have this column. So all I have to do is bringing in the price. Just expand on it and you get more. That's my revenue. That's this one as the last one in of average price. How could I calculate this in this case? Because remember, to some of products, is always adding summing. So how could I get the average price? Well, I could get the sum of price and then divide by my count. Right again, it's the same thing, but let's just not be lazy. Let's just write this really quickly. Are exceptions. Come first. I know that I want the or conditions, so I'm just going to do to additional brackets. This equals this plus game bracket. Keep forgetting that. But when I'm going to show you what happens if you don't do the bracket, so let's do this equals this without bracket while you let's just put the brackets. So now I just have to add this one you see here. I don't need a bracket. I don't have an equal sign so I can leave that as is, and that's going to be May some off price. So then I can just say, divided by this one. I've already done the calculation here. That's going to be my average price. That's just double check average price. Obviously, these air dynamics. So if I change this to Company B or C, the results changes well. You see why some product is such an unusual and useful function? It's not just the sum of products, but you can use it as a count if function is some if function and also a look up function. Now, pay attention to your brackets, though, because their placement is really important. If you're writing this for the first time, that will check your results and then trust it. If you ever forget about the syntax like what was the plus four? Can they use that? Was it the ass traits or the coma or the brackets? Come back to the demo workbook to refresh your memory. There is no shaming that I do it myself Now this return, friend exercise.
Leila Gharani, Excel Instructor, Business Consultant
