All language subtitles for 06 - Calculate running averages

- [Instructor] Collecting data over time

lets you perform many different kinds of analysis.

One useful way to analyze your data

would be to find a running total based on monthly data.

You might collect sales broken down by month

and then want to find a running total,

that is, a total of all the months

up to a particular month that is listed in the table.

In this movie, I will show you how to create Excel formulas

that find running totals, running averages,

and also three-month running averages.

My sample file is an Excel workbook

named 02_06_RunningAverage, and you can find it

in the Chapter02 folder of the Exercise Files collection.

This workbook contains monthly customer data,

so I can see the number of customers

who have visited my store.

And let's say that I want to find a running total.

In other words for February,

I want to know the total number of customers

who have visited the store up to that point,

so January and February.

Then March would include March, February, and January

and so forth.

To start, I will click in cell C2,

and I will create a formula

that I can copy down to the remaining cells in this column.

So I'll type equal and then sum,

and I want to define the range of values

that I want to look at.

The first value is in cell B2,

and I don't want that value or cell reference to change.

I want it to always look at B2

and then update the last cell in the column

that's going to be included.

So I'll press F4 on Windows to make it an absolute reference

with the dollar signs, then a colon, and I'll type B2.

I will leave this reference relative

so that it will change when I copy the formula,

and I'll type a right parentheses to close out and Enter.

And I get the value of 4,969.

Now I'll click cell C2 and copy the formula down

by dragging the fill handle until it covers cell C13.

And you can see that the formula has been copied down.

And if I look in cell C3, I see my range is B2 to B3.

C4 is B2 to B4, and everything else all the way down.

Do note that there are error indicators

in every cell in this column

except for the first and the last.

And if we look at the error indicator,

it says that this formula in the cell refers to a range

that has additional numbers adjacent to it.

So when I'm looking at the values in B2 to B4,

I'm excluding all the values from B5 on down.

And the reason we don't have an error indicator at cell C13

is because it does include all the values.

If you want to hide this error indicator,

you can either do it for individual cells

by clicking the icon that shows the error

and then click Ignore Error,

or you can go to Error Checking Options

at the bottom of the popup menu.

That opens the Formulas category of Excel Options.

And then you go down to Error Checking Rules

and clear Formulas which omit cells in a region.

Click OK, and the error goes away.

Now it's important to know

that you have turned off that error

for every workbook that you open in this copy of Excel.

So do be aware of that

that you will no longer see this particular error.

If you want to calculate a running average,

you do the same thing as before

except you use the average function.

So I'll click in cell D2, =AVERAGE,

and we're starting again in B2.

It could be very tempting

to use the data next to it in cell C2,

but remember, you're adding those values together.

It's a running total, so don't make that mistake.

And yes, I have done that.

So I have B2.

Press F4, type a colon, B2, right parenthesis, and Enter.

Good, my average is 4969 'cause I only have one value.

This time I will double click the fill handle

so I don't have to drag.

And my average doesn't change very much,

and it looks pretty good.

If you want to do the same thing

for a three-month running average,

then you start when you have three months of data

and you use a slightly different cell range.

The third month in my collection is for March,

and that's in the fourth row.

So I'll click in cell E4.

I'll type an equal sign and then AVERAGE.

And now I want to create a range that the start and finish,

the beginning and end of the range, will both change.

And that's because I want to look

at the most recent three months.

So in E4, I will start with B2,

then a colon and B4.

And it can be very tempting to make B2

an absolute reference, but don't do it

because you'll be doing the same calculation

as you are for the running average.

So I have B2 to B4.

Enter. So I have 6,798.

That's because the formula in D4 and E4

look at the same range of cells.

But now if I double-click the fill handle next to E4,

you can see

that my last three-months' running average changes.

And that's because it keeps looking at the values

from the previous three months.

So in cell E5, we're looking at the values from B3 to B5,

E6 from B4 to B6,

and that goes the same all the way down to cell E13.

And the three-month running average

for December looks at cells B11 through B13.

As you can see, you have a lot of ways

to summarize your data, running total, running average,

and three-month running average

are three very popular measures for business data.