[SOUND] Calculations are really the engine room of our spreadsheets. They bring our data to life and help us to extract information from it, so when we construct formulas, there's really no room for error. Now Excel has very clear expectations when it comes to the syntax of formulas and functions. So even a small mistake like missing a bracket or a missing argument will produce an error message and we're prompted to fix it. However there are more subtle mistakes we can easily make, and as the tasks for our spreadsheets become more complex, we will do well to follow the same guidelines that we set out in the previous video to do what we can to prevent those errors. Accuracy is of key importance, so we need to have in built in checks and balances to trap any potential errors in our calculations. So that our spreadsheets remain flexible and responsive, we need to design our calculations so that formulas will update automatically and not break when changes in other parts of the workbook occur. We also want to ensure that our workbooks remain easy to maintain and user-friendly. So we must make sure that others can understand our calculations, and trace how we have arrived at an output. In other words, our calculations must be auditable and transparent. Now Alex has done a little bit more work on the presentation for his report. Based on his client's requirements, he needs to create a chart showing the average sales per month over the last 12 months. He also wants to get the average price for both houses and units, the monthly change as of the last month, and the highest price. So the next step is to build calculations for this report. As we discussed in the previous video, we want to keep our calculations separate from the raw data as well as the presentation itself. So what we're going to do is create another sheet for our calculations. Okay, now we'll come back to this in a minute. Let's pick up where we left off at the end of the previous video. We set everything up to save the user the hassle of having to type in the Region once they enter a Suburb. So there are few things to add on the Data worksheet. Now we said that we want to ideally keep our calculations away from our raw data, and for that we usually put them onto a separate worksheet. But in this particular case, it does make sense to put some calculations with the data. So we're going to ensure that all our calculations are on the far right so they don't interfere with where the users are entering the data. Now coming to our Region, first of all let's select all of the Regions over here and get rid of them because we can replace them with a calculation. Now we created the Lookup data last time and now we're going to use that. One of the first decisions we have to make is which function should we use. One that might readily spring to mind is the VLOOKUP. This is a commonly used function. It's well known and it isn't a totally bad choice in this case, but the VLOOKUP has some real limitations. If our columns move around, we might add some in the future, for example, it is going to break. It is also not as auditable and can be quite inefficient. So a better option here is to use an INDEX combined with a MATCH, and that's what we're going to use in this case. Learning to choose the right function that's a good fit for your purpose is an important skill to learn. Make sure that you choose a function that's a natural fit for your calculation. Now, exercise some caution around volatile functions like NOW, TODAY, as well as OFFSET and INDIRECT. We'll look at the last two later in the course and they do have their place and purpose. But volatile functions recalculate with every change you make in your spreadsheet and as result they can seriously slow down your spreadsheet. So make sure you only resort to them when there is no other alternative. Okay, now let's get into it. We're going to start off by typing equals INDEX, and you may remember this from the previous course. The first the INDEX wants is what array we want to look up. Now if we hadn't named our range, we would have to click into the next worksheet and select the entire array. But by naming our range, we are not only making it less error prone to create the calculation, but we also make the calculation easier to understand for ourselves and other users. So we are actually going to be looking this up in our Region and I'm going to select that and press Tab. Now to work out which row in that Region array I need, I'm going to use my MATCH and I'm going to match my Suburb. Now once again we've named the array already, and we've named it sensibly into the Suburb named range, and this is an exact match. So close the brackets for my MATCH, close the brackets for the INDEX. It's actually not much more complex than the VLOOKUP, in fact I think it's easier to understand and it is much more flexible. And when I press Enter, the formula fills in automatically for the remainder of the column and that's because I've used a table. So you can see already that some sensible design choices previously are helping us to create a flexible and error free workbook. Now that this is done, let's create the next calculation. We want to create a chart showing the average sales per month over the last 12 months. So we will have to pull back values that meet a certain month, and year combination. That's quite a bit of information to go into one formula. Now some say the rule of thumb about the length of calculations is to make sure that your formulas are no longer than the actual length of your thumb. Again there are no hard and fast rules but generally, we want to make sure that formulas are easy to read and easy to understand. So keep them simple, keep them short. Often it is better to use the helper function to break up your calculation rather than having one formula that tries to do everything on one go. So rather than having to constantly work out the month and the year from the date, we're going to add a couple of little helper functions to produce that information for us. So first we need to work out the month for each of the dates. Now for this we're going to use Excel's MONTH function and what that does is it literally return the month number of the specified date. So I am going to type in =MONTH, click on the Date, press Enter, there we go. And then I am going to do the same for the year. Okay, the only thing left to do is to label our columns appropriately. So this is going to be our Month, and this is going to be our Year. So now we've added nice consistent calculations but we kept them on the far right from where our users will be entering data. We may also wish to lock these cells in the future so that the calculations are not accidentally overwritten by another user. We'll look at this in a later video. The other calculations however, we're going to put onto our Calculations tab, so that's where we're going to now. Okay, before we can do anything, we're going to have to work out which is the most recent date that we've got data for, and then we're going to work back 12 months from there. Now even though I'm just in a kind of rough calculations workbook, it's really a good idea to label everything. So this is going to be the Last Sale Date, and then I'm going to use my MAX function. Now we don't want to select the entire column. This is another common mistake. We have over a million rows in Excel so that is necessarily going to make for a very inefficient formula and slow down our spreadsheet. When you're working on tables, it's so easy to select all the data in one column with one click. Aren't tables great? And because our range will grow automatically with the table, we're not hard coding our range but at the same time we're also not working with more data than we need to. Now close the brackets and press Enter. So this looks a bit weird at first. The data is shown as a numeric value. When that's the case, just come up to your formatting and change that to something more suitable. So now we have our Last Sale Date. What we need to do next is work out a little table which has the dates starting in each of the months for the last 12 months. And from there, we'll get the month, year, and then we can work out how many sales we've had for that month, year and type combination, and that is the data we're going to use to generate our chart. Now we want to work out the first day of the month, 12 months prior to this. And there are quite a few approaches you could take, but again we want to keep it simple and find the best fit. People might be tempted to use an IF function here. The IF function is powerful and very useful, but it also tends to be overused and it does potentially trigger performance issues that result in slower spreadsheets. So use the IF only when it is really needed, when you have for example, a real need for branching conditions. But what we're working with here are dates. So we're better of using a date function and Excel has fantastic range of dates for us to work with. We'll look at some of these in a little more detail later on in this course, but just for this one example we could use the end of month function, EOMONTH. If we get the end of the month 11 months ago and then add one day, that will give us the date we want. Let's have a look. So we're going to type equals EO, select EOMONTH and here's the date we're starting from. We want to go back 12 months, so we're going to minus 12 which will actually get us the last date in the month 12 months ago. And then having got that we're going to add one to that date to get the first date of the following month. And what we're hoping for here is the first of the tenth, 2016. So press Enter, beautiful. Now the next thing we're going to look at is working with some helper columns. I could extract the month for each formula, it wouldn't be difficult. But given that I'm going to have to do it for house, unit, and townhouse, it means I would be performing the same calculation three times, which is inefficient. I would be better off performing it just the once. Also by using the helper function, I make my formula simpler and more transparent. So it's definitely not cheating to use helper columns, it's actually really good practice. Okay, now to get the month from the date, I'm just going to use my MONTH function. Click on the Date and Tab, then get to the year where I'm going to use my YEAR function, click on the Date and Tab. What I need to get now are my average sales for this month-year combination where the type of property was a house. And for this, I'm going to use the AVERAGEIFS function. Often in the property market you actually see that they typically use the median, but working with medians is a little more complex because we must match month, year, and type of property in our case. And we don't want to get booked down with complex formulas just yet. So we're going to cheat a little bit and use the average instead. Okay, so we want an AVERAGEIFS. Now our average range is going to be our price, and once again, I'm going to come back to my Data tab and I'm going to select the Price column. In the next week, we will show you how you can actually use these structured references, that's how you refer to references in a table, without the necessity to click back into the table. But for now, let's leave it pretty straight forward. Then type a comma. Now our criteria range, we actually have three of these. Let's start with the Type. So we're check that our Type is in fact, and I'm going to have to come back to my Calculations tab, the same as whatever is at the top of that column. But now I must use a mixed reference because I want this to stay still when I drag down, but move when I go across. So I'm going to press F4 twice to get the dollar just in front of the six, which means lock in row six, but not column D, okay. Then another comma. My criteria range 2 is going to be my Year. So back to my data, I'm going to select my Year and comma and this time I need the year to be equal to whatever my calculated year is here. And again don't forget your dollars. This time of course we're looking at the column rather than the row. And then finally, I need to check my month. So back to my data to select the month, and I need my month to be equal to my calculated month, and again apply your cell referencing. Close your brackets for your AVERAGEIFS and Enter. So there is my AVERAGEIFS for my House and if I drag that across, it will break because I'm working with table references and we saw this in the last course. Now in the next week, we will actually show you how you can achieve the dragging but for now we're going to keep it simple. We're just going to Ctrl+C and paste it into these two cells. Okay, now we're almost ready to drag down, there's just one more thing we need to do. We need to actually add a month to each of these dates, and one of the easiest ways to do that is using the EDATE function. So I'm going to click EDATE, click on previous date, and I'm going to add one month to it, there. Okay, again let's format that as a date. Now we will cover EDATE and EOMONTH in more details later in this course. Now we can drug that down until we get to the first line 2017. Right, now we have 12 months of data and we can select the rest of these and just double-click to copy it down, and there's the data we need for our chart. Next up, let's take a look at how we can use formatting to improve the readability and functionality of our spreadsheets. [SOUND]