Transcripts
1. Introduction: Hello and welcome to the first class off the ultimate excel course. My name is Alan Murray, and this is Excel Formulas made easy. So what are we gonna talk about during the class? Well, we're going to start by talking about the anatomy often excel formula. So we're gonna go right from the beginning. I'm gonna write some simple formulas and talk about a different elements that make up. I excel formula throughout the course for at this class will be covering eight Useful Excel functions that you can put into practice immediately. We're gonna look at how we can reference different worksheets and workbooks. Very odd. An essential skill off. No in excel formulas. This you'll definitely have a requirement to reference different worksheets and possibly tour a lesser extent. Different fire was completely different. Workbooks. I'm gonna look a calculator difference between two dates on both calendar dates and also just working days using a very clever little function built into excel. Who's gonna look a calculating percentages very hard to avoid the use of cat claim purse entities. Nearly everybody needs that kind of skill. So we're gonna look at some typical examples. We will be cat late in what a percentage of the value is such A. What is 10% off that value? We'll be looking at how can calculate no presented off like a discount or adding the organs , such as in a bonus or added tax. I was gonna cover absolute cell addresses. Now, if you've seen these before, this is we have dollar signs such as Dollar de Dollar five around a reference cell dress this used to fix those addresses on. We'll see what that's all about and what was going to cover the county function. Now this is one of excels, most useful and well known functions. There's gonna get some coverage at the end of this class really, really useful function. It's something that I have benefited from many times in my life, and I'm sure got lots more uses where that's gonna come to my rescue as well. So what are we waiting for? Grab a coffee and click the button below to enroll now
2. Introduction to Formulas - Writing your First Excel Formulas: hello and welcome to the first video off this course on talking about writing formulas. Now this video is aimed at beginners to formulas were going to go completely from scratch and learn the anatomy off. How they're written doesn't matter what formula you will be using and just in this video, looking at using Excel like a calculator. So we do in some basic arithmetic, and we'll move on to using excels, functions and more advanced versions in over later lessons off this course. So here we have four numbers in cells A to to a five that we're just going to mess around with for the moment, right? Different formulas on. So these numbers don't really mean anything to us right now, but in later lessons will have more real world examples. For now, we just want to get to grips with the anatomy often excel formula on. The first thing we need to know when writing formulas is that they begin with equals. Formulas always begin with equals. That is what tells excel. The a formula is coming. The next thing you would typically do is reference a cell so I can click on so a two for example, which contains the value of 40 but it could contain some text as well. It just depends what formerly your writing right now. It must be a number. It must be a value because we are going to add another value onto it. So I have typed my plus sign quiches above the equals in a UK language keyboard. It could be somewhere else as well, depending what keyboard layout you're using. And I'm then going to click a different cell such as a three, and notice how Excel changes the color off the cells and the reference to them of in the formula. Now that might not look too impressive at the moment, because I've only got two sales and they're only one column or kind of two columns away from where the formula is being written. But please very mind that it doesn't matter how advanced or how amazing you get excel when it right in formulas, the fetter exile does this, and a lot of other stuff we will cover in later lessons is gold dust. It really helps keep track of where you are and what you have done, so it's easier to identify problems and also see your progress Right now, I just need to press enter to confirm that formula is very important. You press enter, just clicking somewhere else will offer, not get the job done. And sometimes you'll run into problems. Gotta be careful. Clicking sale was When you're in a formula, we will press, enter or return as we may know it, as that will confirm that formula and presents the answer of 140 which is obviously 40 plus 100. And if I click on that cell, I can see the formula written in the formula bar above at the moment, just a top here and also even without seeing it in the formula bar above. If I double clicked the cell, I would see it within this editing mode off the cell Arkan Empress escape to a bought editing mode and come back to the normal, a superficial level of excel. If I was to double click back in to edit that formula again, I want to mention that all o of reference cells in both sides of this formula, this equation, it doesn't have to be that way. I could delete a three and type in what they would call a constant type in a value I could do a two plus on in five. So that's a constant because it doesn't change. Whereas if our reference a cell that has the potential to change if people or of another formula effects that cell but presenter now it's simply 40 plus five, so the 40 has the potential to change. I could change that now Toe 80 and now is 85 but a five is not going to change. Listen, go zines had the formula and actually manipulates it manually, said s our first formula that's have a look at during a subtraction formula. So once again you would click in the cell where you want the answer to go. So I'm going to put it in see for type equals to begin a formula reference the sale that you're interested in or type of number. But you're typically referencing sales 90% of the time, putting your hyphen sign or your minor sign and subtract another value, such as are you five right now. And that was subtract 15 from 100 producing 85 if any of those values to change the formula will automatically update with the current result. It doesn't contain the result. It contains the formula but displays the results. A que for multiply weaken type equals again reference a cell again. This time the operator is the asterisk or the star Stein that is above the eight on my keyboard right now, Please bear in mind. The symbols can move on different layouts of keyboards, but it will be that star or that asterisk or that snowflake. Whatever you may you like to refer to that symbol or that operator, as within canon, click a different cells, such as a three once again order color code and going on a four multiplied by a free press . Enter very important press enter, and that confirms the result, which is 5000. And the underlying formula in place last formula. For the moment, divide is left to do equals to start reference. A cell divide is the forward slash the one that is underneath the question. Mark my keyboard right now, the same one we use in dates and infractions. We also uses a divide sign because it's an equals here. Exile, noses, formula and not a fraction, and then I can click a different cell. Such are you for I don't do. Are you free? Divided by a four, which currently 100 divided by 50. Therefore, the answer is to So they are a basic arithmetic in a moment. Add, subtract, multiply, divide being done within excel so kind of formulas we could write in a calculator. But doing in Excel on the key advantage being that these forms have the potential to update automatically no more manual processes. As this course goes on, we're going to look at more intermediate advanced base formulas and more looking include in the functions of Excel, which a lot of people's formulas are utilizing. So don't feel that all formulas are going to be like this, and that you may need on some kind of intermediate maps, level skills or something to get by. And exhale is certainly not the case. You know those skills a helpful but Excel takes a lot of this stuff on for you as long as you know how to write how to construct a formula. And that is what this part, of course, is all about
3. The Order of Calculation - BODMAS: in this lesson. We need to have a quick talk about the order of presidents. Now. Excel can simplify our formulas when we get to writing functions, as a lot of the processes is done for us and built into the function itself. But we have to be a little bit more careful when we're right in their own four meters about specific mathematical principles that will still exist on what we're talking about. Here is the rule of body mass or the rule of bid mess as a lot of people now it as otherwise known as the or the order of presidents. So as an example here, I've got two numbers on screen. And let's imagine, as a formula, I wanted to add the two numbers together to plus free, which would be five at a. Multiply them by that first number again, the to so two plus freeze five moat blood, but who is 10? But the answer is not 10. It is eight because of the rule of body mass or order of presence on. What that will stipulate is that multiplication has greater priority than edition, so it performs the second part of this formula first and then adds the other one on afterwards. So it does the free multiplied by two. Producing six goes back to add to which then produces eight, and to get the first part of this formula to operate. First, we need to wrap it in those parentheses or brackets to say I do the at B free plus C free. And then once you have an answer, a k five, multiply it by be free, and now we have the value of 10 on this is normally referred to was the Order of Body Mass or Bid Mass, which is simply an acronym to remind us that divide multiply, have a greater priority than adding and subtracting in formulas. The bees, your brackets. So all in all, this is your brackets. Order off operators, and then we have our divide multiply, add and subtract on. This is just an acronym to help remind us that their order exists. I don't think the acronyms that that used for myself it's just remembering it. Divide and multiply have greater priority unless you get into quite in depth formulas. It's not something that your general exhale user has to fear too much just bear in mind that this principle exist for when you are writing your own formulas and you may need the use off these brackets to stipulate the order off the calculation.
4. Formulas for Calculating Percentages: in this lesson, we're going to start talking about calculating percentages now. From the next lesson, we will start to begin talking about functions that built in formulas of Excel, which typically dominate most people's formulas. But when we were just talking about writing raw formulas without the need of any function, percentages is going to be one of the most common needs to do so because so many things from life are represented as a percentage such as tax and discounts and inflation, and so on that are needs to be able to work and calculate them is quite important. So I've got a few examples here to work through, and I want to start with Cell D for where we have been tasked with calculating 15% off the value in Cell B four and there's quite a few ways that we can do this. For starters, we could just type equals reference that cell with the value in and then multiply it by 15% and that will tell us what 15% off that value is, which in this example is 75 Equally. We could also go back into that formula and instead off reference in 15%. It wouldn't be uncommon in my training sessions for people toe. Ask if it's OK to use a decimal so we could type 0.15 or indeed even just 0.15 and that would work just as well. So 75 is the answer again, but representing 15% in decimal form, as opposed to percentage for. But I think most people would be mawr comfortable looking at a percentage we could even refer to a cell. Such has the cell above, which has got 15% routine in it. As long as that sale was formatted as a percentage, then by referring to it that would work as well. It depends with their benefits us to refer, to sell or to actually type the value in. But I read your work now. In the next example, I want to increase the value by 15% so here we could type equals refer to that value, and one way of doing it would be to multiply that value by 1.15 or 115% and that would add the 15% on top of itself. So now I've got 575 off. So we know that 75 busy ants already here we could even do something like B seven plus B seven, again multiplied by 15%. That's another way of doing it, which seems a bit longer winded than just multiplying by 115%. But a lot of people are trained, actually find that a little bit easier to understand because we're doing it in two parts here. Remember, the rule of bottom s would stipulate that multiplication happens first, and that's a good thing here, because that would calculate what 15% off B seven is, which is 75. That number is then added on top of B seven, producing 575. The exact same answer that multiplying it straight by 115% would give you sing. See, we've got quite a few options here, and you just want to choose the one that you feel more comfortable with, especially if you're not confident doing this type of stuff. In the next example, Weaken do a very similar thing to what I was just speaking off, but now would decrease in so doing this one quickly equals the value by multiplying story by 90% is the same as I was just talking of now. But now we're decreasing, said Timpson, off 190 90%. That value would be the same as taking 10% off. It tempts it is 550 drops it for 50 And just like speaking at a moment ago, we could also do be 10 take away be 10 multiplied by 10% which would calculate Timpson beat in first and then subtracted from it but juice in 4 50 ago. In the next example, we're looking at trying to calculate what a number is as a percentage off another number. So for this we just knew to do a little division calculation, it will be equals the value divided boy. The value that you want to know what that is. A percentage off. So I want to know what 70 is as a percentage off 500. So I do the 70 divided by the 500 or, in this case, DeFries divided by B 13. Remember, it's your forward slash for divide. I went up press in tow. I get 0.15 which we can there represent as a percentage by using a percentage format in button on the home tab. And it's 14%. And finally, I have an example off calculating what a number is in competitive. Another number. I like how much it at number, increase or decrease percentage wires. So here I've got 600 on 500. You can imagine that this might be last year's and this year's data or something like that , and you want to know how much did we improve or not by So for this we can do equals and we want to start by clicking on the con a new number, if I can call it that so we can subtract the old value and then we're going to divide it, Boy, the old value. But once again, we have to remember our rule of bottomless because it will come into play here because we need to do the subtraction first in the way that I typed it. But Aurora body Mass would stick play. It will do the division part first, so we're going to wrap this subtraction part of that formula in inside brackets to force that bit first on, then divide by be 16 and when I press enter, I have 20% because 600 is a 20% increase on 500. And that is just a few examples off working with percentages on doing some typical formulas around that A some fernet is quite important to get used to because a good chance it's going to come into your your business at life somewhere along the line, and he may have to do some basic calculations with them.
5. Unleash the Power of Excel Functions: Let's start to have a look at some of the built in functions provided with Excel, and in this video we're going to cover the mind five aggregate functions, which are some average count Max and Min. But we are also going to get a good ground in in the basic anatomy, off a function certain it is something we can build upon as we progress tomb or intermediate and advanced level functions. So beginning with Cell D four here were off, set up, ready for a typical some function example, and we would like to some the values in a one to a four. And as we covered previously in a lesson, we could write a formula off equals so a one and then simply plus a two and then plus a three and so on and so forth. But if we've got thousands off these values, that's gonna take a long time. So that is obviously not the approach we will take. We are going to use the auto some button sitting on the far right off the home tab on by clicking that it will write the some function into that cell, and hopefully you'll see some similarities with the formulas we have written previously, although have not written a function yet. But what I mean by that is he noticed that they start with equals and you can see that there are some brackets or parentheses in here, and these will follow the rules that was spoken about before. So we've got an equal sign that we've got the name of the function, and then we have these brackets. There are always brackets after a function name, always on we've in those brackets. That is where you feed the information that the function needs. And just underneath that function right now, I have a little tort it box just talking to May and providing some arguments. So I've got argument number one argument number two and then their arm, or to come as a some function. Can some many ranges of numbers The first argument or the first question as a normally luxury for today's is mandatory? The 2nd 1 is optional. Square brackets around it indicate that an argument or a question is optional. So from here, you can see that the sum function has had a guess that we went to some the range a four fruit at sea for silly three cells on the left of where I am that is incorrect so far. Highlight that range and select the range of values I want to use, said it right standing. I could have taught that myself with the range operator in between, a one fruit a four could just press enter. And now I have the total off those numbers if I revisit that function, the some off a one fruit for. So I had to help excel out a little bit more there because off the positioning of where my result was going to bay. If I was in the cell directly underneath those values and I pressed that auto some button, it would have automatically guessed that there the numbers I want and in this case, they are. But that's not always the case, so the fact that it is highlighted ready for you to change it on you can go and select whatever range on the sale that it is that you're trying to. Some is very important, and he makes you nice and quick. You can get these sums very quickly. There are numerous ways that you can run these some functions. So don't be surprised if you see your colleagues or somebody on YouTube taking a slightly different approach. One last approach, which is unlikely. You're going to take with some, but it is very likely, or something I will be doing with other functions in his course is just a type in, So you're unlikely to do that with some because it has its button up in the ribbon. But the other functions do not have their own button. You can access them at the top, but you have to search for them because they don't have their own button. So is normally quicker and easier just to type these functions in. So if I type equals and begin to type some, you don't have to type in uppercase. He can see Excel scanned through its hundreds of functions, and as soon as I see the some function, I could double click to insert that to save me, type in the rest. If I did type arrested myself, I would need to type the open bracket in to force the argument. You're taught it to pop up. You notice when I double clicked or if I press my tab key on the keyboard moved down with the arrows and press tab that would put the bracket in for me as well. I can, in highlight the numbers close off the bracket, press enter, and I have a some. So that is the first example of function, the most popular function of all the some function. But and we also have these four. They're kind of grouped together, So let's roast through those quickly with average. Looking back up to that some button, I could click the little arrow that drops down next to it and he conceive, Got those five functions that are mentioning on the sheet. If I click on average, that will prompt me to think it has the range. That's obviously wrong. I'm going to highlight the four numbers that I want, and this will give me the mean average off those four values, which is apparently 71.25 I can always hide those decimals if I wish. With these two Blue arrow buttons on the home tab on decrease those decimals, it might do an element of round in as you operate with them, but that was the right value. We then have the count function. Once again, I could go for the little drop down arrow next, their auto some button select count numbers, as it's called up there. But it's actually called the Count function. And then I could highlight the range of numbers that I want on. This one is going to look a little strange because it tells me this four, and that's pretty obvious for us that this four. You don't think we need the function to tell us that. But just imagine, when you're dealing with much larger ranges in this that changing frequently, this functions more important and more useful than maybe it's, I necessarily looks like right now. Now, with the max function, let's very things up a bit. That's type this one in Robert and used. The drop down equals Max open bracket. Highlight the numbers, close off the bracket and press enter and then we've mean let's go back to the drop down. I could select men select the numbers, and these two are providing the biggest maximum on the smallest, the minimum at values in that humongous range of sales I have there. So this video serves as a nice introduction to thes functions. Some is very important to know the others against lesser and depends what you're going to be working with. But it also gives us a nice ground in into different ways that you can write them ways that excel speaks to you with that little box underneath. In the arguments on, we're going to see us again and again as we progress for this course and right Maura Mawr formulas and functions.
6. Referencing other Sheets and Workbooks: in this lesson, we are going to look at how to reference other worksheets and other workbooks within your four meters, because at some point you're bound to need to be able to do this, especially reference in other worksheets, which is much more likely. So I've got some data in different worksheets here I have a London sheet, a South Hampton sheet on a Cambridge sheet that will look quite similar. It's just monthly expenses from some different outlets that we have. Don't we have this overview sheet to go through some different examples off how I can reference those? So let's start. We have this London cell in Soc to where I just want to total the London expenses seller numbers over here on the London sheet. So just like we saw in the previous lesson in Cell C to weaken, click the auto some button, and when we are prompted for the range of numbers, simply go toe over to the London sheet and reference those cells now notice. The Sooners off clicked in London and I haven't selected those cells yet, but at the top in the Formula bar, it is referencing that London sheet I have London exclamation mark, and there is always an exclamation mark after as sheets reference. Now we don't really need to know that, because Excel does it will for you. As you can see right now, it writes all that for you. But having some of these skills does make you a better Excel user. If you're aware, that's what an exclamation mark means, and it can read other people's formulas and understand what's going on a bit better at once . For off selected London, I can in select their cells, and it's simply right. Be free to be nine into that range. I can impress, enter, and that will confirm that formula and bring me back to the other sheet to the overview sheet with the answer 10,700. Now let me click on that cell when deleted one moment and show you a common mistake. When people are new to doing this on the just gonna work off some of that rust if they've done it, if they do it a few times. But you know, some people still make this mistake, let me press my son. Button is ready for the numbers. I will click on London. Highlight the cells and then click back on overview without pressing. Enter on it. Changes to formula to look at the overview sheet. Be free and benign and in people presenter at this point. So you end up adding the wrong cells on the wrong shaped. Once you've gone over to the other sheet, you have selected your sales. You just carry on with your formula. At this point, I'm finished our press enter being other formulas that we're covering this course we may type of combat and carry on and do something else. At that point, what we don't want to do is click on the overview sheet unless I'm sure I want to go there and click on the sale or go there and do something, because when you click on that sheet exhales going to write in a reference to that sheet. And that's normally mistake that even some experienced excel people have got and you know they curse themselves when they make enough to delete it and do it again. It's all quite frustrating. Even worse, we're not so confident and it goes wrong. You don't know why now, with the Southampton shaped is on a similar story here. I could press my daughter some button, select Southampton's Selector, Sales and Enter. And just like we saw previously, I could even type in the some function open bracket. I could even type in stuff like Cambridge if I really wanted to. It's a bit of a long way around. During all, this would be free to be nine, but it's absolutely fine to do so. It is the Cambridge sheet. Be free to be nine that we need the hot likelihood of me knowing no sales will top. My head is OK. I know it now, but in reality is unlikely. But there's more than one approach here. Let's look at another way off, getting their total of all of them another way of referencing different shades. No, What I want here is I want a total sorry. Wrong. Sell off all of these numbers now. I could just do a some function and add up those numbers. Yes, that would make life Asia, But that's not what this video is all about. So pretend they're not there for a moment, and I want to add up. Be free to be nine on the London sheet. The Southampton She on DNA, Cambridge sheet all in one formula. No. One to create free formulas and into 1/4 to add those up, under or in one. So I'm going to press the auto some button because I am performing a some exhale Promise me for C two to C five. That's obviously the wrong one. I'm going to click on London sheet and select. Be free to be nine Now, looking up in the formula bar, I have what we saw earlier now. One thing like could do here is I could type in my comma to move on to the second argument , which can see in my box here Number two. I could go over to the Southampton sheet, and I could highlight that range as well. And he can see in the bar of now. Got London Be free to be divine, and also South Hampton be free to be nine. I could even put in a comma and then going get Cambridge, be free to be nine, and that would be absolutely fine to do so on. If these ranges were different on each sheet would probably take that or a similar approach . As it happens here, though, the ranges are exactly the same. They're extremely consistent, so I just delete that for a moment. Here's a nice little short cut for it. Let me go over to the London sheet. Highlight. Be free to be nine and that that point hold down the shift key. That was the shift k off the keyboard and click the Cambridge sheet at the bottom and look at what is written in that formula bar. It's written the some off London range operator London to Cambridge. Be free to be nine. So it is. Half the size of formula is what it would have been with the other approach. Because the ranges are the same. Simply put in a sheet strange, say look from London all the way to Cambridge. Be free to be nine. But presenter. How have the answer is important for that specific technique that London's a Cambridge sir is in a row. It's in a kind of sequential order with no sheets that are not interested in breaking up. You know, if the overview sheet was in the middle here, that would cause a problem. I can't do that technique, but after I removed the sheet or do the technique I showed with comments or something similar. But that is another way of referencing sales on other sheets of in a formula. Let's go and look at reference in other workbooks within a formula. So what we want to do here is I've got this month's expenses. We just created that in C six. But I've also got enough workbook with last month's expenses in, and that's already open. So if I go to my view tab, click switch windows, you can see last month's expenses. I have a work but called last month's expenses, and here it is, and there's the expenses total. So that's what I want to use. I want to know what is the difference, but switch back to this total 24,000 and 1/2 pretty much on this total just over 25,000. And let's imagine that's a typical thing that we do wrong Button there, switch back. So in Soc eight Aiken type de course on reference the cell that we just answered without previous formula and subtract the cell on the other work, so I will click on my M switch Windows button last month's expenses. It's important the workbooks open at this point because once I'm in a formula, I can't go on open. It needs to be open, ready last month's expenses. Click on that cell. And if you can see the formula bar at the top, this is what it's looking like. I've got C six take away and then we've got the workbook name here. There's the workbook name followed by the sheets name followed by the cell reference as an absolute reference. We're going to talk about that in the to a free lessons time. Thes dollar signs make it. Absolutely I'll explain or that at the moment no is to see six on the overview sheet. Exclamation mark. There, off this workbook square brackets around the workbook name. It's all been written for you. So what? I'm saying it Please don't get caught up in it. How? Well I remember its next benchmark. What if I forget? Square bracket? Oh yeah, If you forget, it is not gonna work. But you're probably not typing in your probably just clicking a sale like I've just done on Excel does it all for you and you just press enter and it takes you back to your sheet with the answer on. Our expenses are down by 6 90 which is good because it's an expense. And that is how, with reference to sell in this shoot on a cell in a completely different workbook. So this is how that formula ended up looking like. But this is because that sheet artery that workbook is still open. What I would like to do at this point is switch windows back to last month's expenses and I'm going to close it. That's close that workbook file close and thats going. Look at formula now and it looks a lot. Yeah, a lot bigger and scarier because, well, that workbook is closed. We're going to have this entire path to that file. This part is not so bad here. It's just a desktop of my one drive my user account. But imagine how crazy somebody's can look, depending what they're called and how deep in the structure they are. And it's not a problem, you know, assumes open it, fight up. The phone will be shortened to this. If I may even interested. I just wanted to show you what it looks like. So if you see a huge file path from someone else's formula on one of yours and it didn't look like that when you created it. But that's okay when it's in a closed A is gonna show the exact location off that workbook in addition to its name.
7. Calculating Date Difference including Working Days Only: Hello Now, in this lesson, I want to start looking at calculating dates because working with dates is a very common task for Excel users, some trying to bring in some date examples for out this course. Now, in this first example, we just simply want to know the difference between the current date and a date in the future. And as you can see on screen, my future date at the moment is twinge if of March 2019 which at the point that you watch this video might not be a future date, but the time of recording it's the sick for November 2018 on that date is a few months in the future. In fact, it's early next year compared to the current date, and we want to know how many days until that date imagine in in business scenarios that it's some kind of due date, maybe of a payment or something. Or maybe it's the expiry date off a contract or a membership or some kind of deadline. So this is going to be a simple formula, because remember, this data is stored. His numbers in excel so in Selby to weaken simply right equals to begin a formula, select the date and sell a two because that is the future day or a larger number. And subtract today's date, which, as I say for me in a moment, it's the sixth of November 2018. But it doesn't matter what lays, because I need this formula toe work tomorrow and next week and next month and next year and so one. So we're going to type today, which is the name of a function in Excel that just calculates what today's day is. So it really does have the perfect name by being called today, and it does a very simple but very useful job. Now it's going to get the dates from the computers clock, so I double click its name to finish off its name and putting open bracket in underneath. You'll see that it does not prompt for any arguments is one of those very rare instances off a function with no arguments. So we put close brackets straying away, which looks a little bit strange, and it is in a way, a little bit strange. But it's a function that's going to get the date from the system clock it needs no help, no info from us. But the bracket still have to be used always with functions. When a press enter, I get what looks a little crazy, but that is the correct answer. But it's being represented as a date, which is wrong but excels trying to help us, it sees that were worked with that formatting keeps the forwarding consistent, so we just need to select that sell because it's no good to us. And using the drop down on the home tab or any other means just changed the format to general. Because this is just that a whole number is how many days of a general former is perfect for what we need. And apparently there are 134 days between the date of recording this, the current date and that date in cell A to so if this state and sell a two was to change what has changed in March to May, for example, that will quite happily recalculate. Let me just put that back to march, and but I can't really demonstrate is awfully when tomorrow comes. That's going to drop to one free free for me. But that's all going to be dynamically done. Now let's take that a step further on in Cell C to calculate the number off working days between today's day in the date and sell a to now. For this, we can use a function called network Days. So it's two words in one really net work days, network days, and you can see there are two kind of versions off that function. We have the kind of normal one, if you will, and then what? They're called in the international one. So the 1st 1 or just assume that Saturday and Sunday is a weekend on that you would like to ignore it. So it works on your kind of Monday to Friday Working week. Where is the 2nd 1 is more flexible and would allow you to put custom parameters for a weekend and to specify that maybe it's only a Sunday. Or maybe it's Friday and Saturday. So is much more versatile, which is going to use the 1st 1 for this demonstration. And if I double click on that, I'll get the prompt for the start date, then the Indict and then an optional argument for holidays. So let's get straight stuck in the start date. That is today's date, and I'm going to use that today Function from the previous example Tell calculate what the current start days to calculate. What today's date. So it's dynamic. As time moves on, I'll put my comma because I mean a function now to move on to the second argument on the end date is the date in Cell two, and there, it is, sure was going on with those brackets there and one more comma, we leave us with the question called Holidays. And yes, people's first fought is bank holidays, etcetera, which is a good four in a very common use of it. But this could be any date that we are looking to exclude from this function, so that could be for many business or just life reasons. And in this example, if I zoom out off that, I have some dates on the next sheet for click on non working days. Here I have these free dates in cells, a two to a four, and I can simply highlight those dates and you can see in the formula bar above how it references the non working days sheet and then a two to a four, just like we were talking about in a previous lesson on how to reference data. Another sheets. So you get more familiar with that, and you'll get used to it as you write more formulas that do work of course sheets. But here I just want to reference those free clothes off the bracket on the end and press enter. I get the answer of 94 94 Mondays to Fridays between today's date that date and also exclude in any of these. If they fall on a Monday to Friday, there's to finish in formula. So between today's date in the day, in a two also ignoring dates in these free cells. And it's another function under our belt, which maybe will find useful in the future. Maybe we won't. But even if we don't, it's all good practice at writing formulas, especially when you're new to this getting familiar with putting in your commerce reference in sales nest in functions, referring to data that's another sheets starting your formulas of equals on order, things that the Mawr Mawr, you use them. This will just become more more natural to you, just like learning any skill
8. Understanding Absolute Cell Addresses: Now we need to start talking about absolute sale references because you're going to see them a lot in Excel use, especially when writing formulas. So we've got the two formulas from the previous lesson. I have the one calculating the days difference on the one calculating at the days difference, only working days and including the data from the other sheet. Now, if I select the formula and Selby to and if I feel that down or copy it down, it will work a dream because that formula references the cell to its left and, as I copy it down, all of the other formulas reference to sell to its left. And you can see our highlight in blue shows the grid reference but really extras to sell to its left. And he's known as a relative cell reference because the references relative to its location the one on the left. Now, when we get to this formula and I feel that one down, I get results. But I also get a few warnings now. This does not necessarily mean that they're wrong. I want to be clear on that. If you have got spreadsheets and you see these little green or yet arrows or triangles, then that doesn't mean it's wrong, although it might be. It's just Exhale, Queary and what you have done, Andi. If I click on one off the sales with the warning, I'll get a warning sign next to it. Well, if I consume one Internet for a moment and hover over again, it tells me, Had a formula is referring to arrange with additional numbers next to it. And if I click on that icon or have options toe or ignore it to fix it, if they're right in, they're thinking what's going wrong here or to go into the settings and start turning off somebody's functionality. Now I'm going to leave that alone because I want to have look it it ourself and get a deep understanding of what's going on and why do we not want it all? Why it? Sometimes we do want it. If that might sense, let me zoom back out on there and let's look on the formula, instead said a formula is used in today's date. The cell to to the left, which is company a free, That's good. But then we get to this part and it's looking at cells a free 85. And if we go look on that sheet, a Frito A five is this range, so it's not using the first date. The 15th November is missed it out. And that's why the Harris says about the formula, referring to sales with a range. They've got additional ones next to it. There is the additional one next to it. That's what they were trying to say. Now that for us is bad. We want that date. But just bear in mind that sometimes you may have been after that behavior. Now, as we go down the list, you consider moving down a four a six. This one is a five to a seven or a 6 to 8, and you can see how as off copy that formula down each cell. The reference to these free have also been moving down one cell at a time every time to skipping away from the range, which we did not want. We do want the reference to this state to move down to a free dance, a Ford and a five. We do want this to move off a to two A for all of them should use a to a four. Sorry, sorry. Quite a lot of talking there, but just trying to really bring some clarity to what's going on here. Now when we go to the first formula, let's look at fixing that on. In this formula. I can select the range A two to a four, or sometimes that can be difficult without. I've got a mouse here with me at the moment. So maybe just click on part of the formula. I can click to the left of the A after the four in the middle of the end, for it does not matter, and I compress the F four keep. So there's a function key at the top of your keyboard. F four on that will put these $2 signs in, and that is known as an absolute reference. I can click on a to press F four $2 going again. I could have just typed those dollar signs. No problem there, but F four is a little short cut to putting them in, and the dollar signs go before the thing that it fixes. So dollar a fixes column A and dollar to fix his road to and then again dollar A for column . A dollar four for four. So the use of dollar signs is quite strange. He's got nothing to do with money, that's for sure. It's simply a symbol in Excel formulas. That means to kind of stop moving or to be fixed on the official name is absolute. That is an absolute address, absolute reference. So when a press enter, nothing really happens. But when we click on that sale and copy it down and taken no off those answers as we copy it down, the answers change on the green triangles disappeared because now when we look at those cells, the reference to a four it's still on. This row is relative to that cell. But this is steal a to a four on the next one down. It's still a two to a four, so that reference has been fixed or made absolute, and that is done with dollar signs proceeded nothing that it fixes or makes absolute or on your keyboard. The button F four is your shortcut as you select these ranges to put those dollars in nice and quick
9. The COUNTIF Function - An Incredibly Useful Function: let's finish this section off the course with a look at the count if function now. This is an incredibly useful function in Excel, and it will get Cem more coverage later on. In this course, we're going to cover it again and in slightly greater depth on more, more elaborate examples. But we're going to talk about it now as well. And I also want to use this function as an opportunity to mention a function wizard as well , which is not something you tend to see or hear me talk about too much. Because I like to write my formulas into the cell. I think it's faster and from a learning point of view. Also, you understand things a bit better, but at the same time a wanna mention dysfunction wizard. A lot of people are trained like it, and it's certainly not a bad thing. So let's go there now. The function I want is count if, as I say and what we're trying to do here, as you can probably guess, is count how many people say yes and how many people said no in this massive list off attendees responses. So in cell E three I could type equals count if because that's the function I want and I know that, but I could also click so a free and click this little F X button next to the formula bar to trigger what's known as the Function wizard and that will ask me for what function I want. And it gives me the opportunity of type in a description to find it a little bit like a search engine or to go and find it in a category for the From the Formula Function Library . We've also got a selection of kind of recent ones here. Counted happens to be there, which is, say, coincidence, really, and also a link to get more help cities function. Wizard does provide a lot more tools to assist you in getting a job done. However, it does slow you down a bit as well. So straight away I've got kind of select the function potentially find it, you know, account. If he's not always sitting on the top there, ready for you, where is a typing in? Would have gone straight to the point a lot quicker. Let's imagine it's no in that list down there and in my description, I'm going to write the words count If sale equals yes and let's see what it does is it likely won't go. And I got a rare functions. Come back, there's count. If so, there's some our recommendations somehow before it, even though the 1st 2 words were counting. If but never mind, it did come back and also have a little description underneath about what it does select count if and click OK takes you to a window called function arguments. Now I keep saying that the information in between the brackets separate but commerce is called. Arguments on this improved to that here. So these two things will be separated by commas in the brackets when this formula is done, bit of information underneath to help us get the job done right now. Now, the range of sales that we are testing is range B three down to be 11 so I can select that . And if I zoom out for the moment, you can see that's okay. Let me zoom back in now for the criteria. Now I want to know the ones that say yes. So I'm going to type Yes, in you can see it could happen in the cell, in the background there as well as in here and exhibit information fed on the riot. Bit of information below The fact that says equals zero in a moment is not a good sign. But if I click OK, it works The answer. Six 12 free for her 56 And if I go and look at my formula equals count if be free to be 11 coma. Yes, but with the speech marks on these double quotes now, I didn't type those. And when I was in the function wizard, it was saying the answer zero. And that's why I needed those double quotes and I didn't do them, which is why it was showing me the wrong answer. But then it went and worked because to function Wizard, actually put those in for me Now, please do not be misled here. The function weird is it is not just going to finish off your formulas all the time for you . Now you have to get pretty close and there are certain things it looks for, and I happen to know that's one of them. So as a demonstration, we saw how it helped us get the job done, whereas maybe that was something we forgot. But it's not always gonna be there for us. It's useful. But there is a reason you don't see people use it too much as well. And then that's mainly a speed thing. There's nothing wrong with it. It's just people find type in, gets the point a lot quicker, really. So that's all well and good. I could do the same thing for no and get free. And indeed, if one of those nose became, yes, that would re calculate to set and as a side. It's a really, really popular function out there. It's changed about, you know. Now I'm going to re explore that formula, double clicking to go back into it and instead of saying yes, which is absolutely fine. But instead of that, I could refer to the cell to its left cell D three. Notice how I said that cell to the left relative reference. So sell duty free and when we copy it to the cell below Cell D, for the one to its left has got the information. I'm looking for a K yes, and also no B three to B 11. No, when I copy that formula down, that's going to change to be four and B 12 because it will move down. And that's no good. Because then I'll miss John Hollins response. So I'm going to fix that. And f four was the button dollar signs go in absolute reference. Relative reference. Great opportunity for me to recap on a previous lesson there as well. Press enter filled a cell down three as well. Look at a formula cell to the left. Absolute reference. Be free to be 11. Another example of them in action. Also seeing the function count if which is quite brilliant and also seen the function wizard in action or in this video alone, let's look at another example off the county function that this time we're focusing on numbers, said a previous example. Was looking for text values. The word yes, the word no on. We wrap them ivory in inverted commas refer to a cell, but now I've got numeric values. We have percentages, we have scores and we want to know how many passes and how many fouls. Let's imagine that the past right is 75%. Let's imagine that that's the case. So further passes. I'm going to write the county function in this time because it's a little bit quicker and all the aforementioned reasons. So if I zoom in a little bit more just to make sure it's quite clear, equals counts if helps. If you type it right, count if oh strong again, Here we go and it prompts for the range. And father criteria, which previously were two boxes in a window, could function arguments. Now the range It's the same as before really be three to be 11 different data, but it's the same people in the same range of cells. And I'm going to press my F four key on my keyboard to make that an absolute reference so that when I'm finished, I can copy it to the cell below and tweak it to get number of thousands. Comma. After that prompts for the criteria. Now we want greater than or recourse 75%. Now, this is the first time we've done this in this course, but it will get spoken about quite a bit more overcoming lessons we need the greater and sign and that is above your four stop. Normally it is on a UK keyboard, followed by equals followed by 75%. We wouldn't know if it's more than greater than or equal to 75%. I can enclose my bracket now. That would be quite a normal approach. You know, we may not all of knowing about these logical symbols. A lot of you reading this might be quite comfortable with them on love you might not be so comfortable at The less than sign is above the common next to it, the arrow facing to the left to the riot. Positive. Greater in Now the press enter, I get an error message that is no correct. Apparently, Now the interesting thing or what I think is interesting is when you click OK, it takes me to before the great and on time notice for Click Over Here presenter. Wrong Click OK, takes me back here, so it's clearly tryingto say that something's gotta happen around here now, as we can probably have guessed. It's the double quotes, although it's not a text value this time. Whenever you right the criteria into the sale Sorry into the formula, you have to use double quotes. So previously when we referred to a cell, it did not matter. But when we typed in like the word, yes, we needed quotes. And it may look strange here because this is not text really is kind of a number, but it needs to govern this text now. What I would like to do, though, is click in the Function Wizard, the FX above, which opens up function arguments because notice how this helps you as well for still wasn't sure. The issue is it is screaming, invalid. And this argument is clearly here where the problem is and telling me the answers invalid and in down below a little description. You can put it as a number one expression or as text Andi click back in the cell above. Hey, did you see that assumes are clicked in a cell above it. Put the quotes in again, just like in the previous example in the other sheet. Sever, click OK, order a sudden for have the right answer a little bit of stumbling around on. Maybe I learned from that and then I copy the formula down again over six. But if I go into that formula. That is a fixed reference that doesn't move. This is the same as before. Double quotes a in for us. But I need to change that to a less than sign because 75% oh more is 75%. Anything under is a foul. There's your three. And if this 71% for Kerry became 76% they switched to seven and two. And if I undo that, sits back as it waas. So in this example, we tested for a number, in which case we needed symbols like greater Than and less than equal to. But we still needed to double quotes, and I wanted to do it wrong to start with also show you how it was warning me about a problem on the sheet as I wrote it. But it also when I went in the function wizard, how it warned me there and also helps me kind of fix it. So these different techniques of understanding off our options getting used to how Excel talks to is all going to make us better at writing formulas on this count. If function is a really useful one to know as well, so any excuse toe cover in different scenarios on the course is all going to be quite beneficial as well.
10. Thank you: okay? You made it through the class. I want to take this opportunity to thank you for your time. And I hope to see you again soon in one of my other classes. If you have a question at all about anything we've spoken about during this class, please post it into the discussion area. I will do my best to reply as soon as possible. And if you did enjoy the class, please leave a positive review. It really helps toe for me to put the word out there on for others to benefit from this class also.