Formulas & Functions I Excel Bootcamp Part 2

1. Formula fun intro: Being able to write formulas and use functions is a crucial part of working in Excel. However, is also something that a lot of people struggle with. But knowing just a few fundamental things will enable you to use any function available in Excel. And that's exactly what this part of the axon boot camp training is about. Hi, I'm boss and I'm Trainer and Consultant for axial Power BI and Tableau. I wrote my own company, data training IO, and I'm also a YouTuber. I've built this complete online actual training to help you master Excel in the quickest way without wasting time learning things that you want to use in practice. But this training is the second part of the axon bootcamp where we will talk about essential things and best practices for working with formulas and functions in Excel. 2. Creating a simple formula: This section we're going to have a look at formulas and functions, something that you will be using all the time when you work in Excel. Now, formulas basically just let you do calculations like in a calculator. But in Excel, you can do much more once you start using formulas in combination with functions. For example, to clean up your data. Or maybe you want to extract data using some of the lookup functions. Now, let's start all the way at the beginning and go over some basics first, the Excel workbook for this part you find in 0 to four mana fun and then go to Z11 formulas and functions one-to-one. Now open up the Excel workbook. And this workbook we're going to start on the very first sheet, which is 01, creating formulas. Now here we have exactly the same summary overview that we have built in the previous section, but let's now add a few extra insights to this dataset. Now, starting off by adding the averages for the value columns, for example, the booking window, Let's say we would like to have the average booking window for hotel one. And what I'll do now, how could we do that? Now here, let's go to the cell h 11. And here we can do our average calculation by starting off with an equal sign. And as soon as you start diving, you will see in the formula bar and there we have now an equal sign. This is always the start of a form. Now, if we want to calculate average, what we could do is sum up all of these values and divide it by the count of bookings that we got for that hotel. So we sum up all five values and divide it by five. This will be one option. Now, how could we do that? We could go and hard-coded values like this. 35 plus 31 plus 36 plus 50, plus 30, and then divide it by five. Now what we are doing here is wrong because of two reasons. First of all, the division happens. First, because division happens before addition and the sum of the other values. So what happens is a device that 35 and then only as the other values, so we end up with a value of 158. Now how can we change the order of calculation with brackets? So let me go back to that. So now we can put in brackets around some of these values before we do the division. So the sum happens first and then the division by five. Now we do get the Gregg value of 36.4. However, still it's not really good. Now the second reason why our formula is not so good is because we have hard-coded device. That means if one of these values dangers, for example, the first one, let's change it to 40 than my formula doesn't automatically update. To make sure that our calculation nicely updates when the underlying values get updated, we have to refer to the cells that contain values instead of hard-coding values. Now, I'm going to change the 40 back to 35. And let's go back to the formula. Now over here, Let's get rid of the sum of these values. And instead of this, we could also just click on the cell from which we want to have the value. Then again, plus, and then I click on all of the other cells. And you see each time when I have a new cell or new range that gets a different color. So that makes it very easy to find the ranges that you're referring to inside of your form. Alright, now let's press Enter and let's see if we still get the correct result. Yeah, we still have that thirty-six point four. But if one of the values changes, then the result of a calculation also nicely objects. Now you might be wondering, well, what you're doing here is not the most efficient way. I've already used axilla bit. And there's the average function, which lets you do this much more easily. And of course you are correct. However, here we're starting with the beginnings normal formulas without using any function just yet. So we will get back to this very soon when we start talking about functions. Alright, so now that we have a formula that works, how can we do the same calculation for all of the other columns? So the length of stay, number of rooms, nightly rate, and total revenue. And then also do it for hotel too. You don't want to do this manually. Instead of that, we're just going to copy this out with Control C. Then select the cells where you want to base it to, and then Control V to paste. And here you see we have different values for all of the gods. And that is because when we copy a formula to the right, then all of the cell references, they also move to the right. So here for the length of stay, let's click on it. Then go to the formula bar. Now you see we have the sound references to the values inside of that column. What if we take a Formulas and copy them down to hotel six? So let's do that. Let's dig or formulas control C to copy. Then go six rows down here for DO2 Control V to paste it. And you see also here the cell references moved six rolls down. So here we have the average booking window for D2. So this is how we can do normal calculations using formulas in Excel. Now the next thing that we're going to have a look at is conditional format, where we can check different conditions and then see if it's true or false. 3. Formulas for logical comparisons: We have just seen how to do simple calculations using formulas. Now in this part we're going to have a look at how we can do comparisons inside of our formulas. So basically check for logical conditions and return true, it's true and false otherwise. Now, let's see how it works now here we have the same summary overview from before, but now we're going to do a few jacks. Now here at the bottom, I have a few things that I want to check for. For example, the first one here I want to see if the average booking window of hotel one is longer than an hotel too. So here is this value that we have in each Dan bigger than the value that we have in age 60. Now how can we check for this? Now, of course you can do it manually, but in this case I wanted to do it automatically using formulas in Excel. Because if you need to do this Jacques thousands of times, then you of course don't want to do this manually, and therefore, we have formulas. Now, let's go to age 19 and again start with an equal sign, because this is just another formula. A formula starts with an equal sign. Now here I want to see if the value that we have an H done. So click on h tan is bigger than the value that we have in age 16. This is it. We have a bigger than sign in-between. Now, let's press Enter. You see it returns true because the thirty-six point four is bigger than 31, if we would have a value below the 31 here. So let me dive in, let's say 20, done, return false. Now let's undo this Control Z, and let's go to the next one. Now for the next comparison, we wanted to see if the average length of stay for hotel one is shorter than orbital two. So over here we are now focusing on the language day column. And I go here to 20, and that's the next comparison form, again with an equal sign we started. And now we want to refer over here to the value that we have an item. I want to see if it's shorter, lower than the value that we have in 16. So let's click on I6 and you see the operator site that we have in-between these two values, these two sound references is smaller than not. That's presenter is it also returns true because the 2.8 in cell item is smaller than the forefront to 60. Okay, so let's keep on going. So for the next comparison, we want to see if the average number of rooms between the hotels is not equal. So it's different. How can we do that? Now let's go over here to the number of rooms column. And here we start again with the equal sign. And now we want to first refer to data. And now, how can we do not equal? Not equal. You can do as follows, with a smaller than, bigger than sine, so different from and then we refer to the value that we have in j 60. Okay? Alright, press enter. And again it's true because they are not exactly equal. So you see the way in which we can do these comparisons using formulas. So as they say, the only thing that you really need to remember is the operator science. And so bigger than, smaller than, not equal to. And then for the next one there, we're going to have a look at the next operator sign, which is combination of bigger than or equal to. So here we want to check if the average nightly rate in hotel one is equal to or higher than those for auto tune. So here I want to check if the 325 is equal to a bigger than the average nightly rate of hotel two. Okay, Now, let's go here to k 20 to start with the equal sign. Then, refer to the value that we have in the South Keita. And now I want to check if the value that we have in that cell is bigger than or equal to, so bigger than sine equal to sign the value that we have in cell k 60. All right, answer. That's it returns true that this works. However, again, imagine that you might forget in which order to place these signs in-between. Doesn't the equal sign converse or the bigger than sign? Now, if you forget this and do it the other way around, so equal to bigger than, let's see what happens. I press Enter and you'll see that Excel recognizes what you tried to do and gives you an information box, Okay? I believe you are trying to do this. Let me put it the other way around for you. Do you accept this? Alright, we accept plain k and that's it. So, yeah, you see axial tries to help you out here a little bit. Now let's do the very last one where we want to check if the total revenue of hotel one is smaller than or equal to the revenue of order two. Over here, we're going to call them out where we have the dollar revenue. And here we can do an equal sign to start a formula. Then we want to see if the total revenue for hotel one, the 4.7 thousand is smaller than or equal to, smaller than sign, then the equals sign. And we want to compare it to the value that we have in L69. Press enter, and this is false. So now you know how to incorporate a logical conditions in your formulas. Now, the main takeaway is that you have to remember these operators science. So let me highlight them for you. So we have the bigger than side. We have this smaller than sign. If you want to check if something is not equal, then you do smaller than n, bigger than sign. If you want to check if something is equal, then it equals sign. And if you want to check if something is bigger than or equal to or smaller than or equal to, you can do the combination of bigger than sign with an equal sign. And of course this also works the other way around. So let me copy this down. The last one is smaller than or equal to. So now you know how to do normal calculations, you know how to do comparison formulas. The next thing that we're going to have a look at is cell references, because there's sometimes you want to sell references to move when you copy a formula, but sometimes you don't. And for that, we have to have a closer look at how we can fix either row or column inside of our references. 4. Cell references: what's up with those dollar signs: Let's talk about cell references before you have seen that when we copy a formula to the right or down, or up or to the left, then cell references move in the same direction by the same number of columns or rows. Or sometimes this is good thing. Sometimes you actually want the cell references not to move. Now, here we are going to have an example that's based on the same summary overview that we built before. Now I added, however, two new columns over here. Rate after it's gone, one rate after this going to do. Because I want to check if we might want to give a discount to a certain person when they stay for longer time. Before we going to do that, we first want to calculate how much the price would be after these two discount, okay? Now, the first discount rate that we want to apply is 10%. The second one is 20 per cent. That the first thing that we want to calculate is the price after the first discount rate of 10%. And then we do the same thing for the price after the second discount rate, we're just 20%. Now, let's start over here in cell IL-6 and select that cell and start again with the equals sign. Now, how can we calculate the price after that Dan per cent discount? Well, we can do the following calculation. We take the nightly red, Let's click on that case x. And then we want to multiply that an in-between brackets. We can do one minus the 10% discount. Close the brackets. And that's it. Now. First, it calculates the one minus the value in R3, which is Dan percent, so 90%. And then it multiplies that we have the nightly rate, which is 319, and that should give us the discounted price. Now let's see, let's press Enter and it gives us 287. That looks good. Alright, now this calculation we don't want to do over and over again for all of the other cells below it and right next to it. So instead of that, I'm just going to copy that and go over here to that cell bottom right corner, you see my cursor changes and I drag it down over here. But this doesn't look right. What is going on? Why do we have there and error? And why do these values look a little bit off. Now to figure out what's going on. Let me go over here to the value that's right below it. So l seven. And let's have a look in the formula bar. Now you see that my reference is more because I moved my formula down. So now we do not have k six, but we have k seven because we weren't one cell down. Okay, so I take the next nightly rate, which is good. However, now the problematic part, one minus the value that isn't L4, L4, it's just an empty cell. I still want to take the ten per cent discount rate. But because my formula moved down, my cell reference is also moved down. And now we are selecting that empty cell. And that's also why the next one returns an error because one minus, well, the text that we have here, that's not possible. Okay, So it returns an error. Alright, so how can we make sure that the cell reference to the Dan per cent discount doesn't move down. Well, let's go back over here to the very first formula, and let's remove all of the other ones. But I go back over here to our six. Now here we want to fix the reference to L3, which contains a discount rate of ten per cent. And that we can do with donor sites. But the question is, where do we need to put the downsides? Because here we have three alternatives. We can either have $2 signs in front of the one in front of three, or only front of it free or only front of Tao or none. None doesn't work. However, where to put the dollar signs? Which of the remaining three options? Now, let's first put both dollar signs in there. To do this and easy way, you just take your cursor, but it only reference and press F4 on your keyboard. Then you can toggle back and forth between all of the options, okay, Now, I want to have $2 signs in front of the front of the free press Enter. And now I'm going to take my reference, drag it down, and you see, now we do have the correct values. If I now go again to L7, you see we have K7, okay, that is still the same. However, three didn't move. Alright, so we are still taking it down per cent. And over here, if I copy that down here to the button, again, also here, it works, okay, but what if we want to now do the same thing for discount rate too? Now here we could take again the very first formula. Copied one cell to the right. And then over here, 46. Now you see we have 258, which is value that might look okay. However, actually is not. If we go to the Formula bar and check the references, you will see that my reference to the nightly rate now is a reference to the rate after discount one. And here, what about the 10% discount? Well, it didn't move to the 20 per cent discount because they fixed it before. So now, how can we make sure that a formula works correctly? So that takes the nightly rate and it started at 10%, would take the 20 per cent. Now you don't want to do this manually, so we have to check our references again. So let's go back and delete over here. And six, Let's go back to our sex and then we just go referenced by reference. Now, you saw before that case x turns into L6. Now, to make sure that the colon doesn't change, you need to put $1 sign in front of the k. So you always put the dollar sign in front of what you don't want to move, what you want to fix. Now here we want to fix the Golan and not the row number because when we copying down, that reference should still move up and down. Alright, then what about L3? Why did it become an A3? Well, that is because we have a dollar sign in front of the column. So if we get rid of the dollar sign and only keep a dollar sign in front of the three. That means the column is not fixed, but the row number is fixed. Okay? I'm going to press Enter. Now, let's drag it to the right. Let's go here to M6. Is it now it takes the normal Nike rate and multiply that by one minus the 20 per cent, so 80 per cent. Okay, so that looks correct. So now that we have the correct formula, I just copy it from oral sex. Then they said to all of the other cells that we have over here, okay? And this is a working version of a formula with the correct cell references. Now, again, to do this in the most easy way, the shortcut key over here is f, for which lets you toggle between all of the different alternatives. Now cell references and working with these dollar signs where to place them can be quite tricky at the beginning. So it's time for an exercise. 5. Exercise: where to put the $ signs?: Where to put the dollar signs inside of your cell references to fix the columns and rows can be tricky. So let's do an exercise. Now over here on sheet for exercise there, we're going to do the following. We want to have scenario analysis where on the left-hand side we have the different share prices and the projected growth here at the top. Now, first of all, we need to calculate the share price after the projected growth rate. Now let's get the formula to work first before we focus on the cell references, I go over here to E7 and start with the equal sign. Here we want to have the share price 11.19 and to calculate it after projected growth of minus 5%, so it shrink down, we have to do multiplication. We can say multiply this by an in-between the brackets, one plus the projected growth rate of minus five per cent. Close the brackets, press Enter, and that gives us 10.63. Now, how can we now then copy it to all of the other sounds without having to redo our formula over and over again, not to do that in the most efficient way, we can work with the dollar signs. Now here for our first reference, C7, where do we need to put the dollar? Under the sea or in front of the seven? Well, if you have no clue, then just take over here the cell and copy at one cell to the right. Now if we do that, then you see C7 turns into December. So we need to fix the color. Alright, so I'm gonna delete that one, go back to our original formula and put a dollar sign here in front of the god. See. Now, when I drag it to the right, you see a reference is still C7. Okay. Do we also need a dollar sign in front of the seven? No. Because when we take a formula and drag it down one cell, then you see with just takes the next share price, which is exactly what we want. Alright, so the first reference is good. Now let's go back to the original formula and delete the other ones. Now, we have to do the same thing for E5. What happens when I take my cell and drag it one cell to the right if I becomes five, so it moves to the right, which is good because we want to take the next projected growth rate. However, when we go back to the formula and drag it down one cell, then you see if I becomes E6 and that's not good. We want the row to stay the same. So let's delete the other formulas, go back to the original formula. We need now to fix the row number, which we can do with a dollar sign in front of the row number. Press Enter, and now it should work. Let's try it out. Let's copy the formula, select all of these other sounds, and do Control V to paste. And that's it. Now, always double-check that. So just dig around themselves, go to the Formula bar and see if it takes the correct input value. So we have the share price over here. You can see we have the projected growth rate over here in E15. It looks all good. So we've got it working. Alright, so the main takeaway, first, get the formula to work and then just go sell referenced by cell reference. And if you don't know exactly where to put the dollar signs, just try it out, drag it wanted drag one down and see how the references change. Okay, So far we've talked about how to do calculations in Excel, how to incorporate logical conditions, doing comparison formulas, and we talked about cell references. Now, the next thing that we're going to do is talk about functions, which makes our life a lot easier. 6. Aggregation functions: get more done more quickly with functions: Now it's time to talk about functions and function stake your calculations to the next level, but not only normal calculations, it also lets us do other things like data cleaning or extracting data using lookup functions. Alright, but let's start with the beginning. Now, here we're going to go back to a summary overview from before. We do that average calculation because it was not the most efficient way in which we did it. We can do better than that using functions. Here. We wanted to have the booking when the average for both of these models. So let's go back to H then H, then we can start with the equal sign, just like before. But now we're not going to refer to every single cell and then divided by five. But instead of that, we use the average function, which lends us do this average, average calculation in a much easier way. So let's start typing AV and I stop. Don't type in the whole name of the function instead of that. Tried to just start with a V. It gives you a list of all the functions that start with a V and then go to the one that you need using the arrow keys, not to select it. You could use the mouse but try not to. Instead of that selected by pressing the Tab key. Alright? So you see when you do that, then it capitalizes the function name, already has the opening bracket, and then it tells you what it needs. Now, always pay close attention to the syntax. So the structure of the function here, its average function, relatively simple. It just needs one or more ranges. Now where is my range over here? The booking window values for that one. Alright, then we can close the bracket or just press Enter because Exxon knows what you're trying to do and it adds the closing bracket for you. And again, we have exactly same value as before, the 36.4. So that looks good. Alright, now we want to do the same calculation for all of these articles. So how does that work with the references? Doesn't work in the same way. Let's, let's try it out. Okay, So I'm going to copy over here to cell, then select the cells where you want to base their formula to Control V. And we have the same results as before. The cell references. They also moved to the right. Now can do the same thing again, but then vertically copy the cells, but down to about L2. And now you see we have the same values as before. Alright? So what if we don't want to have an average, but maybe a sum or count. Well, it works kind of in the same way. Let's take, for example, the total revenue. Let's select all done. And instead of the average, we could also have done some function, some selected by pressing Tab. Then also here we just need one on multiple ranges. So here, the range that I want to refer to, all these values above it, the press Enter, and now we have this up. Then I can copy it, paste it below here for D2. And there you go. What if we want to have the account? Well, then in a similar way, we could go over here instead of some function, we could go for a GAN function and you will see discount, discount, a discount blank, all different kinds of variations that we can have a look at. However, for now, let's just go for a normal count and then the range stays the same. Press Enter, see it returns, of course five. Okay, so this is how we can do simple summary aggregations using functions in Excel. 7. Logical functions: how to handle more complex logical conditions: So now that we know how to work with some basic aggregation functions like sum, average count. Let's have a look at some logical functions. Now the first ones that we're going to focus on are the add function and our function. So let's go back to our summary overview and extend our analysis by making some more complex comparisons. This time, here at the bottom, I have different things that I want to check for. Now, before, when we were talking about our comparison formulas, we had just one comparison, one logical condition that we were checking for. However, now this time we're going to have two or more than two. So let's see how the AND, and OR function can help us do that. Let's go here to the first one. We want to check if the average booking window and the length of stay for hotel one is long enough for water to. Alright, so two logical conditions. Now, here, I want to check if both of these conditions are true. Only when both are true. I want to return true. And that is what the n function lets us do. Alright, now, let's see it in action. Let's go over here to the cell age 26. And just like before we start with an equal sign, here, we're going to write an AND function. So type in and select it by pressing tab, followed by the logical conditions. Now, here it just needs multiple logical conditions. Logical one is going to be whether the booking window, the average booking window for that one, is bigger than the average booking window for D2. So here we can do it exactly in the same way as before. So I select over h, Then I want to see if it's bigger than age 60. Okay, so just like before, but now it's just the first argument within the function that we can add multiple conditions inside of this add function. So to do that, type in a comma. And now you see over here and logical two is in bold, which means we are now going to define the second logical condition we want to check for. So here we want to see if the average length of stay in item is bigger than 16. Average length of stay for up to now, we have two conditions, but we could add more if you wanted to. And then close the brackets and press Enter. Now here, this is false. Why is it false? Well, the first logical condition was actually true. Thirty-six point four is bigger than the 31. However, the second logical condition, 2.8 is not bigger than 42. And because not both logical conditions are true, our end function returns false. So if over here we would not have 4.2 for the average length of stay forward to, however, maybe a smaller number like one. Then it would return true because now the second logical condition would also return true. Then both logical conditions are true and the N function would return true. Okay, now I'm going to undo this so that we have for point to here again. And let's have a look at the next one where we have a similar comparison. But this time we want to check if the average booking window for hotel one or the average length of stay for which one is bigger than the one for D2. Now, we're not going to use the AND function, but the OR function because now we just want to see if one of these conditions, It's true. Alright, so let's go and try this out. So I'm gonna go over here in H 27, start with an equal sign. Here we can start with the OR function. Now here we want to see again if h is bigger than age 60, done the second logical condition, logical to as bold. And then we can refer to item and see if it's bigger than I6. Okay? Close your brackets. You see the logical conditions are the same as 40 and function, but this time it returns true. The reason it returns true is because, well, one of them is true. The first logical condition over here, It's true 36.4 is bigger than 31. And the second one is not true. But this time we have our function or, the, OR function returns true if one of the conditions is true. So the next one, you can first try on your own. So pause, try them out and then continue watching or just follow along. Alright, so comparison number three. So here we want to see if the average nightly rate and the total revenue for her than one or equal to or higher than for host of two. So let's go over here to j 28 and start with an equal sign. Now here we're going to use it and function logical one is if the nightly read AND gate n is bigger than or equal to, so bigger than or equal to sign. And then refer to 60 than the other tasks that we want to do is, I'll turn bigger than or equal to L 16. That these are two logical conditions in both need to be true for the AND function to return true. And indeed, that is the case. So the end function returns true because here we have 325 bigger than 984,786 is bigger than the 2964. Now what if we would have used the OR function instead of the n function? Let's try it out. I'm not going to retype the whole thing. I'm just going to copy it from the formula bar, Control-C. Go to the cell below, and then I go to the Formula bar basically in there. Why didn't I just drag it down? Because then the cell reference is also moved down and then we have to burn them at the Dollar science. And I wanted to skip that part for now. Okay, so press Enter. And over here we have at the moment the same function, but I'm going to replace the add function with the OR function. Okay? Now, this is also returning true because, well, at least one of them is true, but actually bothered to read this, but at this point, but what if the dollar revenues for older one would fall to, let say 1 thousand, then only the first condition would have been true. Then the function returns false, but the all function will return true. Alright, so now you're able to do more complex comparisons. But probably you don't want to just return true and false. Usually you want to do maybe one calculation if it's true and another calculation if it's false. And for that we need a function which is going to be the next part. 8. IF function + nesting functions: If within an if, within another if: So we are exploring different logical functions and we have already seen AND, and, OR function to make more complex comparisons. But the next one that we're going to have a look at is the f function. And f function is one of the most used functions with an axon because it's so useful. And basically it just comes down to the following. If this done that, now let's see how it works in action. So let's first start with a simple example. Let's go over here to the colon. And a final goal is to calculate the actual rate that they customer has to be on the basis of the length of stay. So if somebody stays for a more nights, then this person gets the rate after discount to 20% discount. If somebody stays two or three nights done, this person gets the rate after this one. And if somebody stays for just one night, they have to pay the full price. Okay, Now, let's get there step-by-step. Let's go to the cell and six. Now here we can start with an equal sign. And let's first check for a condition. Let's see if the length of stay is equal to abandon for now, this is something we have done before. So we can refer here to I6 and check if it's bigger than or equal to four. Press Enter here. That is true because the length of stay on the first row is five. Alright? Now, this is a comparison. Comparisons, these logical conditions is what we need inside of a function. So let's go back and right after the equal sign, we're going to add an if function f bracket open. Now what is my logical test? Well, we just wrote and logical test our comparison formula. Then we go with a cursor to the end and add a comma. Now we go to the next part of the function. For the next argument, we have to say, what do we want to return? If that condition is true? Then for third argument, then we can say, what do we want to return if the condition returns false? Alright? Now, if it's true, then we can return, well, the rate after that's going to. So over here, let's click on rate after they've gone through. Now, you see that these arguments aren't in between square brackets, so the optional, so if you wanted, you could already closet IF function here. Now it returns 255. However, if the length of stay would have been below four, so that's three, then we'll return false. Alright, let me change it back to five. There you go. Now let's go back to our function. Now, if I want to return something different than false, when the condition returns fonts, then we can add a third argument. So we could say if somebody stays for less than four nights, then they pay is the rate after they've gone one, this one over here. Click on cell, press Enter, and let's check if it works. Changed language, Dave, back to three. And now we would have the actual rate of 287. I'm going to change it back to five. You see the IF function is actually quite easy. However, were a lot of people struggle is when you need more than one IF function. So you need to nest IF functions. Now, when is this necessary? When you have more than two outcomes? The number of IF function depends on the number of outcomes that you have. For example, if we have three outcomes, then it's three minus one. You would need to f functions. If you have four outcomes, then you need four minus 13 different functions. Okay? Now, here, let's add a third outcome, which is going to be that if somebody books do a free night, they get the rate after this going on. And if somebody books for one night, then the nightly rate. Okay, so let's go back to our function. Let's go over here. And where we need to go is to the arguments, the third one, value if false. Now let's, instead of ourselves, let's delete it. And now a function basically says the following. If the length of stay is bigger than or equal to four, then return the rate after that's going to do. But if not, well, then we're not sure yet, either denial rate or the rate after this one. So we need another IF function. So let's type in F. Now here the logical test is if somebody is state two or three nights. So we're going to use NOR function. We want to check if the length of stain that we have units x equals two comma. Click on it again. I say x equals three. Close the or function. Okay, Now the OR function will return again, true or false. Now, if returns true, then we want to return the rate after this one. And if that is also not true, then there's only one option left. That person stays only one night. So we want to return the 90 rate. So now we can close the f function and you see we have another closing bracket for the outer ear function. Okay, Now, let's see if that works. We have now a rate of 255. Now, if I change the length of stay to, let's say one, then we have the full price of 319. And if I change the length of stay to, let's say two, then we have the rate after it's gone, 1287. Alright, so the function works. And just like before, we can copy it down by taking the sound control C to copy, and then Control V to paste it to the other ones. Not only want to paste the values of formulas that clicking on the Control button and choose that you want to have only the formulas. And then we can do the same, yeah, To button copy the formulas, the formulas as format. Now let's go back to our initial formula where we started here and six. Now here we can go to the Formula bar and we can expand that a little bit by clicking on the drop-down arrow down the right-hand side. And now we can divide it over multiple rows. So we can place the cursor right in front of the f function Alt Enter, and then it goes to the next line and just divide it over multiple rows like this. And if you want to, you can also indented with the space bar. And then you do the same for all of these other arguments. So here, after the comma, I would put that also in the newline, etc. Now you can just make a phone number a little bit higher and just keep on going like this until you have it formatted in the most readable way. What is the most readable way while the bathroom, I probably would formula like this over here. So over here, each argument on a new line, once you have it formatted, becomes much more readable as actually also easier to write in this way because you see exactly where everything starts and where it ends. And if you're missing a bracket that pointed out very quickly, I'm just going to press Enter and you see it still works. So placing parts of a formula or function on separate lines and working with spaces doesn't affect the functionality of your formula. Now let's do then also the finishing touch, and let's add the dollar revenue. Here. We just want to have the product of the actual rate that person needs to pay and the length of stay. So we can do this by taking the actual rate, multiplying this with the language day. Now we'll just drag it down. And then over here, we do not want the formatting, so say fill without formatting, and then we can also copy it over here to the other cells. So this is how you can use an IF function. You see the basics are pretty straightforward. However, as soon as you need to combine multiple F's and maybe also with other comparison functions like orange and then it becomes complex. So it's time to practice with the next exercise. 9. So more practice with the IF function: But just have seen how the function works and how to handle multiple outcomes by nesting IF functions. Now let's do another exercise to practice a little bit more, starting off with a simple IF function with just two outcomes. Now here we have a dataset where we have the sales for different countries. The dark details and the comparison between the two in form of the difference in percentage difference. Now, if we want to check if the target cells are above the actual sales, well then we could do that with a function, but actually we don't need an IF function yet. We could also just use a logical comparison starting off with an equal sign here and I5 and then see if the actual sales, It's bigger than the target cells. Okay? That gives me true. Alright, I've done it differently. We could also have looked at the different percentage difference. Now, let's go back to a formula and start with an equal sign and then refer here today percentage. We can check if this is bigger than 0. So that's 0. And press, Okay, now that is true just like before. Now here just pick one of these variations and then we can copy this formula to the other cells, control C, control V. So this works. However, we want to return above diagonal below target, not just true and false. So let's go back to that initial formula. And here we need now an if function. So if this logical condition is true, then return above target, otherwise below target. So right after the equal sign, I'm going to write f bracket open logical test. Well, we just wrote, so that is already there. And then we can place a comma right after the logical test. Now value if true above target. Now here we need to put text always in-between quotation marks. So above docket for text, always in between quotation marks for values don't need it. The reason yet you need to put this in between quotation marks. It's otherwise, it thinks that you're referring to a named range, which is something we didn't talk about just yet. Okay? So just remember, use quotation marks when you want to return text, okay? Now, what if it's false, then we want to return below target. So also here in-between quotation marks below target. Let's close our f function. You see now instead of true, it returns above target. And there where we had false before, it returns below target. So that is working. But let's now make it a bit more complex. Let's add a third outcome. Let's say we want to check the actual sales versus a target. And if we miss the target by more than 20 per cent, that's very critical. If it's somewhere between 10, 20%, that's critical. And everything above it. We don't care. It's okay. So three outcomes. Now, let's go here to J five. And it's diving in our first IF function, if at first logical condition. Here, I usually start with the most extreme outcomes. And so here I start with the outcome be low or equal to minus 90 per cent. So again, Jackie had a percentage column after I started with a function. So f bracket open logical test, H five is smaller than or equal to minus 20%, okay? Now you can say minus 20 per cent or minus 0.2, That's the same. Okay, so don't forget the minus, right? Minus 90%. Alright, then we want to go to the next argument value of true. Well, if this is true, that is very critical. So very critical stacks. So I placed it in between quotation marks. What if it's false? Well, before we define that, Let's just close the bracket and see if this works by center. We see here it returns false. But when I drag it down, then here you see we have two times very critical, minus 36, minus 40%. Okay, so that works. Now, the next thing that we need to do is what if it's better than the minus 90%? So above isn't then critical areas that okay, there are still two outcomes left. Okay, so we go back to that very first formula. And then let's get rid of that closing bracket. Let's extend it further and define the value if false. Now here, we still have two outcomes, so we need another F, So F bracket open logical test. Now we want to check if that percentage that we have here, if it's below or equal to minus Dan per cent. So also here, either 10% minus 10% or minus 0.1. Now, if this is true, then it's somewhere between minus ten and minus 20%. And then we want to return critical, that also here it's taxed. So put it in between quotation marks. And when it is not below minus 10%, that means it's above. And the last outcome is, that is okay. So let's just type in, okay, close the brackets for that they function. Close the other bracket for the outer ear function. Presenter. Alright, now we can copy. Now. I see we have okay, most of the times, but here we have still very critical. And we have one where it says minus 12% critical. I see the other one that's 12% plus, which is okay, that means the sales is above the dog. Okay, now let's go back to that initial formula again. Let's have a closer look. Now also here we can extend the formula bar and place it on multiple lines if you wanted to. However, for now, let's leave it like this and go through it. Now here, the outer logical task that gets performed first is this one here is the percentage value below equal to minus 0.2. In this case, it's not. So it continues with the next one. Here, value of true gets skipped, goes to value if false, then we have another function. If it's between the minus ten per cent and the minus 0 tool by in-between, equal to or lower than. Well, we already checked yet in the first condition whether it was lower than 0.2. So that means if there is a value that is below or equal to minus 10%, it must be somewhere in between. So we don't necessarily need to write an OR function here. If it's equal to a loan and then Present Dan critical. That is also not the case. There's only one outcome that's left. That means is high in n minus 10%. And then we're done. If you're just starting off with IF functions, you probably need to repeat this a few times before it really clicks. Also try it on your own data, but practice makes you master it. The next function that we're going to look at is the f function. So plural. Now, here we were nesting IF functions. Now if you find that a bit difficult than maybe you like the other alternative more, that's overlooked. 10. IFS function: maybe easier?: We have just seen how to write logical formulas that use the IF function. Now, if you have more than two outcomes, then we need to nasty one if inside of the other, if they need as many IF functions as you have outcomes minus one. Now, this can become quite complex when your number of outcomes grows. However, there's an alternative that you might find it easier and that is to use the ifs function. Now let's see how that one works. Now here we have exactly the same example as we had before, but now let's go here to column J and delete the formulas that we wrote. Now we're going to rewrite them using the ifs function. So let's go here to J5, type an equals sign, and open up with the function name. And when you type in F, you see there at the bottom, then we have Fs here, checks whether one or more conditions are met and returns value corresponding to the first true condition. It sounds good. Let's select it. Now here have a close look at the syntax of the function. Now, we need logical, That's one, that's the first argument. Then the second argument is a value of true one. And then we can continue like this logical test to value if true. There's no false argument like you have in the IF function. Now, let's see it in action. Let's dive in the first logical test. We want a dust if the percentage that we have over here, if it's lower than or equal to minus 20 per cent. Now, if this is the case, then it is very critical. So quotation mark and then very critical. So now we can continue with the next scenario. Now here I want to check if it's somewhere between minus ten and minus 20 per cent. Now, here we could do that with an unconditioned, but because this is the second condition that will be checked, we can simply write the following H five and then lower than or equal to minus ten per cent. Now, if this is the case, then we want to return Critical. So now we have defined two scenarios with two outcomes. Now at this point, if this would have been an f function, there would be still an argument. If false, then I want to return the text. But with the ifs function, you cannot do that. You still need to define the last logical test. So here we need to say, okay, if it's five is bigger than minus ten per cent, then we want to return. Okay? Alright. Now let's press Enter and you see the first one. Here, it returns. Okay? And if I drag this down, we have here very critical, very critical, critical, just like we had before. Now also here with the S function, it is very helpful if you divide it over multiple rows. So let's expand that formula bar. And let's place the f function on the next line. And that's added the formatting a little bit. And let's put each logical task or an outcome on a separate row. Now, over here, the closing bracket, I also put it in a new row. And then let's indent each line a little bit that goes inside of this function. Alright? Now, you see it's also much easier to read. So now you have two alternatives to deal with logical formulas where you have more than two outcomes. Either you go for nested IF functions, or alternatively, you go for the ifs function. 11. Exercise: cell references, IF function, and returning blanks: At this point, you know how to use the IF function and you know where to put the dollar signs for his cell references. Now let's combine the two in the following exercise. Follow me to the sheet 09, cumulative sum. And here we have to come for a year, the month, and we have a value column over here, not accumulate some. What is that? Well, that is just the accumulated values. So for January is 850, type in here, even 50. Then for February, we take the 850 in the previous cell, we add the February value. Now I could just take that formula, drag it down here, and that is the accumulated. So that works perfectly. However, let's say that you really insist to use some function. How could we rewrite this? Now let me just delete all of these values that we go over here. And let's go to the February value in R5. And instead of having a four plus five, I'm going to use the sum function. Now, I want to take this arm off. Well, these two values here for January and February. Now we can take this formula and drag it one cell belt and then let's see what happens. You see that we are referring not to the cells January to March, but only February to March. So arrange moves down when we copy a formula down. Now how can we make sure that we always started in January? Well, dollar signs. Now where do we need to put the dollar signs? But let's go back to our original formula here in R5. And we need to fix the row number for the cell reference E4 because we don't want that row number that change. Now what about the column while here we don't really care. We just copy the formula down. So we don't necessarily need to fix the column. So we are going to put the dollar sign in front of the row number four. Okay, and that's it. Now I can take my function, drag it down. So here we have the same result as we had before where we didn't use the function. But you see how we can fix also here inside of our functions, the references using the dollar signs. Okay, now, why did I say we need a function here as well? Well, because the next thing that I want to do is for dose months for which we don't have the actual values yet. There I also don't want to know show the year-to-date sales. Okay? Now how can we make sure that nothing shows here when there's no value in the amount column. Well, with the f function. Now again, let's go back to the cell where we wrote the original formula and delete the other ones. Now let's write an if function here at the beginning. And what is the logical dust? Well, here we want to see if there is a value for that month. So we can just refer to E5 and see if it's different from nothing and nothing you can write with quotation mark, quotation marks. So you are saying you want to return tags. However, there's nothing in between, so nothing, alright, now, if it's different from nothing, then I want to return, well, the year-to-date sales because that means there's a value and otherwise false there at the end of the function, then we want to return nothing quotation marks. Alright, that's closer function presenter. And you see we have 1750, so that works. And what happens when we drag it down? It's still works. But here for the month July onwards, where we don't have any value just yet. They're also know year-to-date sales value shows up. Alright, so that was a small exercise with the f function and the dollar sign. So you've seen now that you can use the dollar signs also for references inside of a function. And that function can also be very nice for aesthetic reasons. Then the next topic that we're going to have a look at is conditional aggregation. So the gown, the function, the average if function and the summary function. Let's go to the next topic. 12. Conditional calculations: COUNTIF(S): We've seen how to do simple aggregations with functions like sum, average, and count. And we have seen how to build in logic inside of a formulas using the IF function. Now, the only thing that we need to do now is put the two together. And we are able to do conditional calculations like some f average if and count them. Now, we're going to start off with the count IF function. We're going to start on. She'd done multiple criteria counting, where we have a simple dataset with sales data for different months and years. And the first thing that we're gonna do is count the number of months where we had sales equal to a high-end. And so we cannot simply do a count because account will give us just the number of months for which we had sales data. Now, here we need to have a count. So let's go to cell H4. We start with an equal sign, then we debit account. And here you see all of the functions that start with God. The one that we need is count F, So counts the number of cells within a range that meets the given condition. So let's select it by pressing Tab. Now here we need to define the range in which we want to perform the count, which is the sales column. Let's select E4 all the way down until e 27. That is the first argument. Then we go to the second argument where we need to define the criteria itself. Now the criteria always it goes in-between quotation marks. So let's start with a quotation mark. And then we put in the criteria which is bigger than or equal to a thousand. And then again quotation mark, and then we can close the count IF function. You see that we're doing seven, which seems to be right because over here we have six months in 2019 with high-end thousand cells and just one month in 21. So now we can go to the next one. What if we want to check for two criteria? So what if we wanted to check if the sales amount is equal to either thousand and the year is 2020. Well then we can use the count function. And also here you see it counts the number of cells specified by a given set of conditions or criteria. Now, this can now be multiple conditions. So let's select it by pressing Tab. And you see that the syntax is just a bit different. Now here we start with D, with Tyrian range one. That's just like before the sales column. Then we go through the criteria. Here we can say bigger than or equal to a thousand. Then we close the quotation marks. Now, we continue with the second criteria range. The second criteria range is going to be on the color. So over here at the beginning of the dataset, and then euphoric with diarrhea. We can put in between quotation marks, the year 2020. Close brackets, press Enter. You see it just returns one because we only have one month in 2020, we have sales over a thousand. Now you might be wondering, what does that actually the purpose of sum F because couldn't we just go to the very first one and turn COUNTIF into account. Let's try this. Can you see it returns exactly the same? And that's why personally, I only use COUNTIFS even if there's just one criteria, range and one criteria, because you never know in the future, you might change your mind in a second criteria. So now that we know how to count if income X function work, Let's go to the next calculation. Here we want to count the number of months where we had sales over a thousand and where the month was either May or June. Now, how can we handle that or condition? Well, let's start again with our COUNTIFS function. And now we have just like before, sales as a criteria range. And we have the criteria which is bigger than or equal to a thousand. And now the next criteria is on the month. So I'm going to select here the Month column. And for the month we have May or June, but let's just type in May for now. And then close the quotation marks and Bracket. Okay, now, let's press Enter. It gives us one because there's only one way where we had a sales amount over a thousand. Now how can we say or June? Well, to keep it simple and we could just copy the formula plus sign and just repeat it and then add the two up here. I just have to change the month of May to the month June. And that gives us two. Now if you would have many months, then of course you can expand the formula bar and just place this on the next line to make everything a little bit more readable, just like this is, there may be another alternative where we don't have to repeat the whole function. Well, yes, there's using arrays now, it's a little bit early to talk about airplanes, but let me show you an example here. Now instead of having this year, I'm gonna go to that criteria where we wrote me. And here I'm going to have a curly bracket open. Then May, which was already there, comma. And then the second value that I want to have inside of this array, which is going to be John. Close the array with the curly closing bracket. Now we can press enter, you will see it gives us spell that spills over to the other cells because it returns actually more than one value. Now, let me show you, I'm going to copy this. I'm going to base it below over here. And this anti cell, you see we have here the value one and over here the value one. So it returns the count if may over here. And it returns the count if june over there. Alright, now, the only thing that I still want to do is sum them up. So let's put this inside some function. Now. This gives us two. So this is an alternative using arrays. Now I'm just going to copy this over to where it originally wanted to have it go. There you go. You might also be wondering, why do we actually need these quotation marks? Well, this is something that you need to remember, but let's say you forget, what can you do that? Well, let's go back over here to that initial formula all the way at the top and age for now, let's say that you are writing your account, this function, just like this. Then at this point, you could also click on the insert function button. Now if you do that, then a pub-sub, There's hopper window. And from here you see all of the arguments of this function. And here below, you see what it actually means where the description. So here we have criteria range one, but we already selected a criteria. Then over here we have the theory itself. Here we could, for example, filling bigger than or equal to thousand without the quotation marks. And once you are done with setting this all up, you just click on Okay. You see it puts it automatically for you in-between these quotation mark and closes the function. So now it's time for you to practice. Try to do the remaining three yourself. Once you're done. In the next section, we're going to talk about sumifs and averages. 13. Conditional calculations: SUMIF(S), AVERAGEIF(S): We have just seen how to do conditional counting using COUNTIF and COUNTIFS. But of course there's also some F or some hives and averages. Now, let's see how they work. Now here we have the same dataset and we're going to do the following calculations that you find over here. Starting off with finding this sum of sales for January 2019 and January 2020. So all of the January's, how can we do that? Let's go to age four and let's dive in the sun. And you see all of the functions that start with some. And in this case we want to have this sum f. Now here you see we have one criteria extra, if you compare that to count them, because now we do not only have theoria range equity, we also have the range of which we want to take this up. Now here it just says range. Well, is that the criteria range or the sum range? It's the criteria range because the last argument, That's the summary. Alright, so in this case we need the Month column. Then what is the criteria that in-between quotation marks? This is going to be the month, January. And the sum range, that is the sales column over here. Press enter and say 1600, which is the sum of the January's. So 750 plus the 850 over there. Now what if we don't want to have the sum but the average for the January's instead. Well, then of course there's the average f function. So let's go here to age five. And now type in average. Use the arrow keys to go down and the tab key to select average F. Now we just repeat or which is net. So here we have the Month column with diarrhea is going to be again January. And now the average range, so that is the sales column. And we have now an average of 800, the average of the 758 on the fifth. So that works. But what if we need multiple criteria? Then we have some F's and average f function. So over here for the next one, we could write some F's and you see the syntax changes a bit because now we start with the summary page, not the criteria range anymore. So first the sum range wages, sales, then the criteria image while we want to have, let's say, the January month column. And then he had a good theory is going to be January. Then we have the second range, which is the year column. Now here we want to have the year 2020. Alright? So you see criteria range, criteria, criteria, range criteria. And in this way you can keep on going. Close your brackets once you've done. That gives us 850, which is just that value for January 2020. Alright, so that works. Now, if you want to make it a little bit easier for yourself, just expand the formula bar and divide it over multiple lines. So over here there's some Fs bracket open. And then here we have the sum range. There may be all of the criteria ranges with the criteria you put on separate roles. Alright, so over here, you can also indented a little bit. And all of this makes it a little bit easier to write. It gives you a better overview. It's also easier for any person checking your formulas. Now, if we want to find the average, of course we can retype the whole thing or simply copy what we just wrote over here. Copy this. Why don't I just drag it down but copied actual formula. Because if we drag it down one row, and then over here you see the range also moves one row down. We would need to add the dollar sign so that the range does it move down, which is something I don't want to bother with at the moment. So here for age seven, I'm just going to get rid of this and copy paste the formula from the cell above. Alright? Then we just changed some Fs to average. Alright? And it gives us the average. Now what if we want to have the total sales in 2021? So for January, February, and March, then we can repeat that same function three times. Once for January, second time for February, and then March. So you would literally just copy paste this over here to the next cell. And then plus. And then over here we can just take this top part, go to the next row over here. Once more. Let me just expand the formula bar and then change January over here to February. And too much. That can happen sometimes that it gives you a correction and asks you, do you want to accept that? Well, why did I get it over here? Because I added a plus sign in the end, which was not really necessary. So double-check before you click Okay, everything looks fine. And now I just make the formula bar smiling and easy. 2500, which looks correct. And what have we don't want to have some averages. What then we just take the average or average IF function, the last one, average sales in 2019 Q2. So that means April, May, June, 2019. How can we do that? Well, it is an average, so we need to start off with an average f function. And then over here we have the average range, which is going to be the average of the sales. And then we can adequate theory and just like we did before, we want to have the year 2019 so they could theory arranges the year column. Then the criteria is going to be 2019. And then we can continue with the criteria range to which needs to be based on the month. Now, let's select the month column and then the criteria. Now, the first option here is to just dive in April, the first month of that quarter. Close your brackets. 1217, then repeat this for May and June. And then of those values, again take the average. Or alternatively we can use these arrays again, which you might want to try, but don't worry if it doesn't work out at this point just yet, but just so that you have seen it again, we could go here just right before the quotation marks of April, curly bracket open, and then save for which ones do you want to have it? So April, then we have me, and then we have June. Alright? And then closing curly bracket. Now that gives us three values. Basically it just returns the average for each single month. So you will see if I press Enter, it spills over to the cells right next to it. Therefore, it's not very visible, so I'm just going to put it over here once or lower than you see the actual values. Here we have the three values. Now we want to take the average of those three. So I'm gonna go back to where it says spill and just wrap the whole thing inside of a normal average function. Okay? It takes now the average of these three values you see below over here. Now let me delete those and go back. Now to end this section, let's take a normal average here of the cells over here, average of the sales column, which gives us 701, but that includes the zeros over here. What if you don't want it? Well, Try to think of a solution. And do you have a solution? Yes, average F, Exactly. Now let's go to cell E3 and type in average f's. Now here I always go for it, if not for the F version. Alright, so what does the average range? I want to take the average of the sales. And then we add the criteria range, which is gonna be exactly same column. And the criteria is just that it needs to be different from 0. Alright, let's close the quotation mark. Close the bracket. And you see that value is considerably higher because it doesn't consider the zeros here at the end. Alright, so now you know how to do conditional calculations using SUMIFS, AVERAGEIFS, and counters. It's time to practice a bit more.