All language subtitles for 01 - Calculate mean and median values

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

- [Presenter] Whenever you start to examine a new data set

the first thing you should do

is discover some basic facts about that data.

In this movie I will show you

how to make several of those measures.

My sample files 01_01_Means,

and you can find it in the Chapter01 folder

of the Exercise Files collection.

The data set that I have

in this workbook shows the number of days in transit

for a number of shipments coming from overseas.

So you can see the first shipment arrived in 23 days,

the next in 38,

and the rest of the data shows

the other days that were measured.

If you want to find the average, or mean, of that data

that would be the sum of all those values

divided by the number of values.

So if I go to cell D2,

which is next to the Mean label,

and remember mean is the same as average.

In D2 I can type equal.

And then the function we'll use is average.

And I will select the data in the range A2 through A12.

Right parenthesis and Enter.

And we get a mean, or average, of 29.45 days.

So just under 29 and 1/2 days on average.

You can also calculate the median.

The median is the middle value in a set of data

that has been sorted into either ascending

or descending order.

For example, if I were to click cell A2

and then on the Home tab of the ribbon go to Sort & Filter

and then click in this case sort smallest to largest,

although I could do either largest to smallest

or smallest to largest,

then I get the values of 23, 27 all the way down to 38.

I have, I'll just count here, 11 rows of data.

So if I go down to the six,

that's 1, 2, 3, 4, 5, 6, the median value is 28.

So if I go to cell D4 and type =MEDIAN

and select A2 through A12.

Right parenthesis and Enter.

And I do in fact, get 28.

One way to examine your data

is to compare the mean to the median.

And in this case, they're very close.

So that means that your data

tends to be fairly well spread out.

To give you an example of how the mean

and median could change,

let's say that we had a shipment that was just horrible.

It took 75 days to arrive.

So I'll go to cell A12

and change 38 to 75 and press Enter.

And you can see that the mean, or average, increased

but the median did not.

And that's because the value of 28

is still in the middle of the sorted list.

So I will press Control + Z to undo my last change.

And we're back to our original data set.

One last thing about the median

is that if you have an odd number of values

then the value in the middle

of the sorted list is the median.

If you have an even number of values

then Excel takes the average of the two

that are in the middle.

So in A2 through A12, we have 11 values.

If I go to cell A13, then let's say I type in 39,

then I can update the median formula

so that it includes the new cell A13, Enter.

And you see we get the median of 28.5,

which is the average of the two values

that are now in the middle of the list.

I'll click to release the selection

and press Control + Z to undo the data entry

and also the formula change.

The final type of measure that I'll describe here

is the mode.

And that is the most frequently occurring value or values.

If I go to cell D6 which is next to Mode Single

I'll type equal, and then I'll type mode

and you see that underneath,

I have mode mult which is for multiple values

or single, S-N-G-L.

I'll click single for now and press Tab.

And I will select the same data range as before.

A2 through A12.

Right parenthesis and Enter and we get 28.

And if I go over to the data set

I can see that 28 occurs three times, 27 twice and 29 twice.

And everyone else occurs a single time.

So 28 is in fact, the mode.

If I were to change the value

at the bottom from 38 to 27 and press Enter,

then you can see that the mode has changed to 27.

And even though 27 and 28 occur the same number of times,

27 occurs first in the data set.

So that means that Excel will select it

instead of going with the larger value.

That's the rule it uses, the first value.

If you do want to find multiple values

that occur most frequently,

in other words, they're tied within a data set,

you can use mode mult.

So I'll go to cell D8, type an equal sign, mode, M-U-L-T,

and note that I still have my change from before.

So I have 27 instead of 38.

Select cells A2 through A12.

Right parenthesis and Enter.

And we get 27 and 28.

In the past, you would've had to identify

the number of modes that you wanted to display

using mode.mult.

However, in recent versions of Excel

there is the spill behavior

and that is what occurred here.

Even though I typed the formula into cell D8

when I pressed Enter

because there were multiple values available

then it applied the same formula

and spilled the result from cell D8 to D9.

If I were to change the value in A12, from 27 back to 38,

then you can see that the mode is still 28,

and you can see that there is only one value

that occurs most frequently in the data list.

So as you can see there are a lot of ways

to get information about your data.

Whether the average, the middle value or median

or the most frequently occurring value, or values,

you can learn a lot in just a few minutes.

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