Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
- [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.