Kvartili

Pomoč pri delu z MS Excelom
Odgovori
4n4
Prispevkov: 37
Pridružen: Sr Maj 09, 2012 9:20 am

Kvartili

Odgovor Napisal/-a 4n4 »

V Excelu imam seznam podatkov, ki jim poskušam določiti kvartile. Težavo imam pri vrednostih prvega in tretjega kvartila.
Za 1. kvartil (to je mediana 1. polovice podatkov) sem uporabila funkcijo =QUARTILE(podatki;1). Funkcija vrne 48,625, jaz pa sem izračunala 46,5.
Za 2. kvartil, sem uporabila isto funkcijo, tu dobim pravi rezultat, to je 58,5.
Za 3. kvartil uporabim funkcijo =QUARTILE(podatki;3), tu dobim rezultat 65,75, izračunala pa sem 67,5.
To so podatki na katerih določam kvartile: 34,5 42,5 42,5 43 50,5 52,5 55 57 60 62 62 64 71 72 74,5 86,5

Zakaj se pojavijo razlike med računanjem 'peš' in računanjem z Excelom? (izračuni 'peš' so gotovo narejeni pravilno)

Pa še primer računanja kvartilov: http://www.mathsisfun.com/data/quartiles.html (zadnji primer je najbolj podoben mojemu, tudi te podatke sem vnesla v Excel in dobim drugačne razultate)
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Kvartili

Odgovor Napisal/-a admin »

Pozdravljeni,

glede na to, da ste mi poslali povezavo na angleško stran, sklepam da poznate angleščino in vam podajam kar originalno razlago, brez prevoda (na vprašanje o izračunu za primer števil {4,6,8,12,14,16}):

The short explanation is that Excel is calculating the quartiles as percentiles. This is really quite different from the way we ordinarily think of quartiles (as medians of the upper/lower half of the data). Here's a quick explanation of how Excel does what it does, using your data as an example. I can't be 100% sure this is the exact algorithm Excel uses, but this will give the same results.
  1. Excel assigns PERCENTILES to each value in the array.

    Koda: Izberi vse

    P(4) = 0; P(6)=0.20; P(8)=0.40; ... ; P(16)=1
  2. Excel then checks where the requested percentile falls in the array.

    Koda: Izberi vse

    For Q1, 0.25 falls between 6 and 8.
  3. Excel then linearly interpolates between these values based on the percentile.

    Koda: Izberi vse

    0.25 percentile is 0.05 percentile higher than 0.20 percentile.
    0.05/(P(8)-P(6)) = 0.05/0.20 = 1/4
    
So, the 25th percentile is 1/4 of the way between 6 and 8. Thus, 6.5 is the returned value. (I realize you typed 5.5, but I checked your data in Excel, and 6.5 is returned quartile. Likewise, 13.5 is returned for Q3 instead of 14.5.)

This of course is a strange way of calculating a quartile and is not to be found on the Wikipedia page about quartiles.
lp,
Matjaž Prtenjak
Administrator
Odgovori