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

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

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.

6:41

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.

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

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.

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

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

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

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