All language subtitles for 06 - Calculate running averages

af Afrikaans
ak Akan
sq Albanian
am Amharic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.