Welcome to week 2, and welcome, Prashan. >> Thank you, Nicky. >> So this week we're going to be looking at text and date functions, which is a bit of a move away from working with numeric data. Tell me, do we have to use these quite often in business? >> The thing is, Nicky, the way organizations are using Excel these days has changed over time. Now organizations are downloading large datasets from corporate databases. And the results are very messy. We need to manipulate the data, and we need to improve it. And manual changes are totally not useful. >> Absolutely, that could take way too long. Can we use tools like find and replace or text to columns to help us with this? >> Find and replace we have covered in the essentials course, which is a great tool to have. Text to columns, because it's really for one-off changes, we'll cover that in the tool box this week. What we're after are automated changes using functions in Excel. >> Could you give us an example of one of these functions? >> Well, one function is the TODAY function, for example. It returns today's date. And using the TODAY function, I could work out how overdue are invoices. Or I could work out when an employee's next performance appraisal is. >> My one concern is we're going to be looking at functions that are quite different from what we're used to. >> Sure. >> And we're even going to start combining some of these functions. >> Mm-hm. >> And in my experience, it's when we start combining functions that the wheels come off. And it all starts to go a bit wrong. >> But that's okay, let's start at the beginning. And work step-by-step by working on the anatomy of a function. In this example, we want to calculate Mei's final mark. We have her Term 1 through Term 3 out of 25, so we need to add these. We also need to adjust the marks by up to 2 to allow for an unfair test. And then we need to add the average test mark for Term 4. We know that all calculations start with an equals sign. So type the =, and you're halfway there. Now we are trying to add, and the SUM function is ideal. So then we type sum, and now onto the second row. After a function name, you always have an open bracket. And where you have an open bracket, you must have a closed bracket. Inside these brackets, we put all the values we want to work with. Excel calls these arguments. The arguments can be values, cell references, or even other calculations. The best way to enter them is to select. So let's select the marks for Term 1 to Term 3. Note that we get B3:D3, the colon indicates a range. In other words, everything from B3 up to and including D3. So far, nice and easy, now we want to also add the adjustment. But we can't use a colon, so how do we do it? How do we tell Excel, and another value? The answer is a comma. Whenever you have more than one argument inside the brackets, you separate them with a comma. Different functions will allow or require different numbers of arguments. The SUM function will actually allow up to 255 arguments. So we type ,2, but it is generally better to click on the cell so we can easily modify the adjustment. We have to include one more argument, and that is the mark for Term 4. But now we have an interesting problem. We don't have the mark for Term 4, which is the average of the other three tests. So we need to get the average. Well, that's okay because you can put a function inside another function. This is called nesting functions. So we type our comma, and type av, down arrow, tab. And because we're using a function, we must have an open bracket. But we don't need another equals sign. Now select the things we want to get an average of. So select E3:G3. And lastly, where we have an open bracket, you must have a closed bracket. And a great hint is that the last bracket must always be black. >> Fantastic explanation, Prashan, and I think that really helps. Because you can use a function for years without really understand where you need brackets, where you need colons. But now we have the rules, we can apply them to different and more complex situations. >> Sure, and this week, we're not just covering new functions, but we're going to look at functions in general. So that we're equipping our learners and empowering our learners to handle any function that they may come across within their workplace during their life. >> And that is so important because we're going to show few really useful text and date functions, but it's still just a few. So I'm going to encourage to not limit yourself to the few functions we're going to show you, but actually take some time to explore Excel's rich library of functions. Go to the Formulas tab, see what's on offer, and don't be scared to try it out. And you're actually going to find, in the practice challenge this week, we're going to give you a function we haven't shown in the practice videos. But you now have the skills to work with functions that you haven't necessarily been taught. So now when the wheels come off, it's going to be your training wheels coming off. And you're going to be just fine, and now it's over to you. [SOUND]
