0:04

[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.

Â 1:31

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.

Â 2:13

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.

Â 2:28

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 a 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.

Â 2:45

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.

Â 4:33

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.

Â 5:01

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

Â here 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 up the month for each of the dates.

Â Now for this we're going to use Excel's months 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.

Â 6:41

So now we've added nice consistence 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.

Â Untable's 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.

Â 8:12

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

Â top 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 purchase 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 rage of date for us to work with.

Â We'll 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, year or month.

Â 9:23

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 EO month 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.

Â 9:55

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 medin, but

Â working with medins 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.

Â 11:46

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.

Â 12:02

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 two 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.

Â 12:59

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.

Â 13:22

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.

Â 13:48

Now we will cover EDATE and EMONTH 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.

Â