Percentielen berekenen in Excel

Percentielen berekenen in Excel

Excel biedt twee hoofdformules om percentielen te berekenen: PERCENTILE.INC() en PERCENTILE.EXC().

Strikt genomen is er ook een derde functie—PERCENTILE()—die werd gebruikt in oude versies van de software (Excel 2007 en eerder) en nu behouden is voor compatibiliteit. Deze oude functie werkt op dezelfde manier als PERCENTILE.INC().

In deze tutorial focussen we op de nieuwere functies—PERCENTILE.INC() en PERCENTILE.EXC().

PERCENTILE.INC()-functie in Excel

Syntaxis

PERCENTILE.INC(array, k)

waarbij:

• array—de array of het gegevensbereik waarvoor het percentiel wordt berekend,

• k—de percentielwaarde in het bereik van 0 tot 1, inclusief.

Beide argumenten zijn verplicht.

De functie PERCENTILE.INC() geeft het k-de percentiel van waarden in een bereik terug, waarbij k ligt tussen 0 en 1, inclusief de waarden 0 en 1. Met andere woorden: deze functie neemt de eerste en laatste waarden van de array mee.

Algoritme

Achter de schermen voert de PERCENTILE.INC()-functie in Excel de volgende stappen uit:

1. Sorteert de waarden van de opgegeven array in oplopende volgorde.
2. Bepaalt de percentielpositie op basis van het k-argument en het aantal N datapunten in de array met de formule: k(N-1) + 1.
3. Als de uitkomst van de vorige stap geen geheel getal is, interpoleert het algoritme tussen de dichtstbijzijnde waarden van de array.

Voorbeeld

Stel, we willen het 30e percentiel berekenen voor een array met gehele getallen van 1 tot en met 10 met de PERCENTILE.INC()-functie in Excel. Zo ziet onze formule eruit:

Opmerking: Hoewel de waarden in bovenstaande array voor de illustratie zijn gesorteerd, hoeven we de waarden niet vooraf te sorteren, omdat het algoritme dit achter de schermen voor ons doet.

Hieronder staat het resultaat van onze berekening:

Mogelijke fouten

• #NUM!— wanneer de aangeleverde array leeg is, of k < 0, of k > 1.

• #VALUE!—wanneer k niet-numeriek is.

PERCENTILE.EXC()-functie in Excel

Syntaxis

PERCENTILE.EXC(array, k)

waarbij:

• array—de array of het gegevensbereik waarvoor het percentiel wordt berekend,

• k—de percentielwaarde in het bereik van 0 tot 1, exclusief.

Beide argumenten zijn verplicht.

De functie PERCENTILE.EXC() geeft het k-de percentiel van waarden in een bereik terug, waarbij k ligt tussen 0 en 1, met uitsluiting van de waarden 0 en 1. Preciezer: voor een array met N datapunten moet de waarde van k aan beide onderstaande voorwaarden voldoen:

1. K > 1/(N+1)
2. K < N/(N+1)

Kort gezegd: de functie PERCENTILE.EXC() definieert percentielen alleen voor kansen strikt tussen 0 en 1. Dit betekent dat er geen waarden worden berekend voor het absolute minimum (0e percentiel) of maximum (100e percentiel), hoewel alle datapunten nog steeds worden gebruikt bij interpolatie.

Algoritme

Achter de schermen voert de PERCENTILE.EXC()-functie in Excel de volgende stappen uit:

1. Sorteert de waarden van de opgegeven array in oplopende volgorde.
2. Bepaalt de percentielpositie op basis van het k-argument en het aantal N datapunten in de array met de formule: k(N+1).
3. Als de uitkomst van de vorige stap geen geheel getal is, interpoleert het algoritme tussen de dichtstbijzijnde waarden van de array.

Voorbeeld

Stel, we willen het 30e percentiel berekenen voor een array met gehele getallen van 1 tot en met 10 met de PERCENTILE.EXC()-functie in Excel. Zo ziet onze formule eruit:

Opmerking: Hoewel de waarden in bovenstaande array voor de illustratie zijn gesorteerd, hoeven we de waarden niet vooraf te sorteren, omdat het algoritme dit achter de schermen voor ons doet.

Hieronder staat het resultaat van onze berekening:

Mogelijke fouten

• #NUM!— wanneer de aangeleverde array leeg is, of k <= 1/(N+1), of k >= N/(N+1).

• #VALUE!—wanneer k niet-numeriek is.

Wil je andere populaire Excel-functies leren of opfrissen? Bekijk dan deze tutorial: The 15 Basic Excel Formulas Everyone Needs to Know.

PERCENTILE.INC vs. PERCENTILE.EXC in Excel

Tot nu toe zien we duidelijk veel overeenkomsten tussen de PERCENTILE.INC()- en PERCENTILE.EXC()-functies in Excel. Er zijn echter ook duidelijke verschillen die ze tot twee aparte functies maken en tot verschillende uitkomsten leiden. Laten we samenvatten hoe de functies PERCENTILE.INC() en PERCENTILE.EXC() in Excel op elkaar lijken en waarin ze verschillen.

Overeenkomsten:

1. Functiesyntaxis.

2. Algemene algoritmelogica (behalve de formule die wordt gebruikt om de percentielpositie te bepalen).

3. Mogelijke fouten (#NUM! wanneer de array leeg is of k buiten het mogelijke bereik valt, en #VALUE! wanneer k niet-numeriek is).

Verschillen:

1. Het mogelijke bereik voor het k-argument:

• PERCENTILE.INC()—van 0 tot 1, inclusief.

• PERCENTILE.EXC()—van 1/(N+1) tot N/(N+1), exclusief.

2. Behandeling van de waarden in een array:

• PERCENTILE.INC()—neemt de eerste en laatste waarden mee.

• PERCENTILE.EXC()—definieert geen percentielen op de eerste en laatste waarden (0% en 100%), maar gebruikt nog steeds alle data bij de interpolatie.

3. Formule om de percentielpositie te bepalen:

• PERCENTILE.INC()—k(N-1) + 1.

• PERCENTILE.EXC()—k(N+1).

Laten we de functies PERCENTILE.INC() en PERCENTILE.EXC() in actie zien door ze allebei toe te passen op dezelfde array met gehele getallen van 1 tot en met 10 en te spelen met verschillende waarden van k:

De bovenstaande tabel laat zien dat de functie PERCENTILE.EXC() niet gedefinieerd is voor k-waarden op of buiten het geldige bereik (k ≤ 1/(N+1) of k ≥ N/(N+1)). Dit is geen tekortkoming. Het weerspiegelt de wiskundige definitie van exclusieve percentielen. De kleine verschillen tussen de resultaten van .INC() en .EXC() nabij de uitersten komen voort uit hoe elke functie percentielposities interpoleert.

Anderzijds moeten we in gedachten houden dat in de overgrote meerderheid van de gevallen te lage of te hoge k-waarden statistisch weinig waarde voor ons hebben. Deze beperkingen vormen in de praktijk zelden een probleem. Bovendien moeten we in het echte leven meestal percentielen berekenen voor veel grotere arrays dan in ons bovenstaande voorbeeld, wat de verschillen in uitkomsten verkleint.

In de meeste gevallen is het logischer om te kiezen voor de functie PERCENTILE.INC(), die ook overeenkomt met de standaardformules die in veel programmeertalen, waaronder Python en R, worden gebruikt om percentielen te berekenen.

Wil je echter vermijden dat percentielen aan de randen van de dataset worden gedefinieerd, dan is de functie PERCENTILE.EXC() de juiste keuze. Die berekent geen resultaten voor het 0e of 100e percentiel, maar gebruikt nog steeds alle datapunten bij het interpoleren tussen rangen.

Het is belangrijk om schone en representatieve data te hebben om er waardevolle zakelijke inzichten uit te halen. De cursus Data Preparation in Excel en de Data Manipulation in Excel Cheat Sheet zijn handige bronnen om te leren hoe je je data efficiënt voorbereidt voor verdere analyse.

Conclusie

We hebben de essentiële statistische theorie achter percentielen besproken: hun definitie, de belangrijkste toepassingsgebieden en de twee belangrijkste functies om percentielen in Excel te berekenen, inclusief hun syntaxis, algoritmes, rekenvoorbeelden en mogelijke fouten.

We hebben de twee Excel-functies ook vergeleken, hun overeenkomsten en belangrijke verschillen uiteengezet, beide functies op dezelfde data-array toegepast om de resultaten te confronteren en geleerd in welk scenario je welke functie het beste gebruikt.

Wil je het volledige potentieel van Excel benutten? Overweeg dan de beginnersvriendelijke skill track Excel Fundamentals, waarmee je via praktische oefeningen zowel basis- als geavanceerde functies en formules leert voor datavoorbereiding, visualisatie en analyse in Excel.