Hoe je betrouwbaarheidsintervallen in Excel berekent

Overzicht van de Confidence Interval-functie in Excel

In Microsoft Excel was CONFIDENCE oorspronkelijk de functie die het betrouwbaarheidsinterval voor een populatiegemiddelde retourneert. De functie is echter afgekeurd en vervangen door twee verschillende functies, die nauwkeuriger zijn afhankelijk van het doel. We moeten de nieuwere functie gebruiken, omdat de oude functie mogelijk niet meer bestaat in toekomstige versies van Excel.

De syntaxis en parameters van de functie CONFIDENCE.NORM

De CONFIDENCE.NORM-functie in Excel wordt gebruikt om het betrouwbaarheidsinterval voor een populatiegemiddelde te berekenen, uitgaande van een normale verdeling.

De syntaxis van de functie CONFIDENCE.NORM is als volgt:

=CONFIDENCE.NORM(alpha, standard_dev, size)

De parameters zijn:

• alpha: Het significantieniveau (1 — betrouwbaarheidsniveau). Voor een 95%-betrouwbaarheidsinterval is alpha bijvoorbeeld 0,05.
• standard_dev: De standaarddeviatie van de steekproef.
• size: De omvang van de steekproef.

De syntaxis en parameters van de functie CONFIDENCE.T

De functie CONFIDENCE.T in Excel is bedoeld om het betrouwbaarheidsinterval voor een populatiegemiddelde te berekenen met behulp van de t-verdeling van Student.

De syntaxis van de functie CONFIDENCE.T is:

=CONFIDENCE.T(alpha, standard_dev, size)

De parameters zijn:

• alpha: Het significantieniveau (1 — betrouwbaarheidsniveau).
• standard_dev: De standaarddeviatie van de steekproef.
• size: De omvang van de steekproef.

Wanneer gebruik je welke betrouwbaarheidsfuncties?

In de praktijk geldt: als we niet zeker zijn van de verdeling van onze populatie of als onze steekproefomvang klein is, is het veiliger om de functie CONFIDENCE.T te gebruiken. Wanneer de standaarddeviatie van een steekproef niet bekend is, dus wanneer we de standaarddeviatie zelf moeten berekenen, is het het beste om de functie CONFIDENCE.T te gebruiken.

We gebruiken de functie CONFIDENCE.NORM doorgaans wanneer:

• Onze steekproefomvang groot is.
• De populatiestandaarddeviatie bekend is (en niet berekend).
• We ervan overtuigd zijn dat de verdeling van de populatie normaal is, of wanneer we de centrale limietstelling toepassen.

Deze richtlijnen geven je een beter idee van wanneer je welke functie gebruikt.

Nu we alles hebben geleerd over het berekenen van betrouwbaarheidsintervallen in Excel, gaan we dat in de praktijk brengen met een voorbeeld.

Hoe bereken je betrouwbaarheidsintervallen in Excel: een voorbeeld

Stel dat jij als data-analist de gemiddelde reactietijd (in uren) van een klantenserviceteam moet schatten. Senior stakeholders willen de effectiviteit van het klantenserviceteam beoordelen.

Als analist verzamel je de reactietijden voor een willekeurige steekproef van 40 klantvragen. Vervolgens zet je de data in een Excel-sheet zoals hieronder (in uren):

Gemiddelde reactietijd-data (deze en alle onderstaande screenshots door de auteur)

Laten we stap voor stap het betrouwbaarheidsinterval bepalen:

• Stap 1: Bereken het steekproefgemiddelde. Gebruik in een nieuwe cel, bijvoorbeeld F6, de functie AVERAGE om het steekproefgemiddelde te berekenen.

=AVERAGE(A2:A41)

Het steekproefgemiddelde berekenen.

• Stap 2: Bereken de standaarddeviatie. Gebruik in cel F8 de functie STDDEV.S om de steekproefstandaarddeviatie te berekenen.

=STDEV.S(A2:A41)

De reden om STDEV.S (steekproefstandaarddeviatie) te gebruiken in plaats van STDEV.P (populatiestandaarddeviatie) is dat we werken met een steekproef (zoals in het scenario staat) en niet met de volledige populatie.

De steekproefstandaarddeviatie berekenen.

• Stap 3: Bereken de foutmarge. Gebruik in cel F10 de functie CONFIDENCE.T om de foutmarge voor een 95%-betrouwbaarheidsinterval te berekenen.

=CONFIDENCE.T(0.05, F8, 40)

De reden om in dit voorbeeld de functie CONFIDENCE.T te gebruiken, is dat we de standaarddeviatie van de populatie niet vooraf wisten en deze zelf moesten berekenen.

We hebben ook geen informatie dat de verdeling normaal is. Daarom is CONFIDENCE.T de best passende functie voor ons scenario.

De foutmarge berekenen.

• Stap 4: Bepaal het betrouwbaarheidsinterval. Het uiteindelijke betrouwbaarheidsinterval volgt uit het bepalen van de onder- en bovengrens.

De ondergrens kun je berekenen met:

LOWER BOUND =(SAMPLE MEAN - MARGIN OF ERROR)

De bovengrens berekenen.

Op vergelijkbare wijze kun je de bovengrens berekenen met:

UPPER BOUND =(SAMPLE MEAN + MARGIN OF ERROR)

De ondergrens berekenen

Nu kunnen we rapporteren dat we met 95% zekerheid kunnen zeggen dat de gemiddelde reactietijd van het klantenserviceteam tussen 3,00 uur en 3,77 uur ligt, in plaats van alleen het gemiddelde te geven, dat 3,39 uur was.

Deze informatie is waardevol voor het management om de prestaties van het supportteam te beoordelen en beslissingen te nemen over bezetting, training en procesverbeteringen om de klanttevredenheid met meer zekerheid te vergroten.

Veelvoorkomende fouten & hoe je ze oplost

Bij het gebruik van de functie CONFIDENCE in Excel, inclusief de varianten CONFIDENCE.NORM en CONFIDENCE.T, kun je de volgende typische problemen tegenkomen:

#NUM!-fout

De #NUM!-fout in Excel wijst op een numeriek probleem met de formule. Deze fout treedt op als:

• de standaarddeviatie kleiner dan of gelijk aan nul is,
• de steekproefomvang kleiner dan 2 is, of
• alpha (het significantieniveau) kleiner dan of gelijk aan nul is of groter dan of gelijk aan één.

Een standaarddeviatie van nul impliceert dat alle datapunten identiek zijn, wat niet zinvol is voor het berekenen van een betrouwbaarheidsinterval. Een negatieve standaarddeviatie is wiskundig onmogelijk. Als je een vooraf berekende standaarddeviatie gebruikt, zorg er dan voor dat deze positief is.

Met een steekproefomvang kleiner dan 2 is het niet mogelijk om een zinvolle schatting te maken van het populatiegemiddelde of de variabiliteit daarvan. Controleer dus of je de juiste steekproefomvang invoert.

Kies een alpha-waarde die groter is dan 0 en kleiner dan 1. Veelvoorkomende keuzes zijn 0,05 voor een 95%-betrouwbaarheidsniveau, 0,01 voor 99%, enzovoort. Onthoud dat alpha doorgaans een kleine waarde is, omdat het wordt verkregen na aftrek van het betrouwbaarheidsniveau van 100%.

#VALUE!-fout

De #VALUE!-fout in Excel geeft aan dat er een probleem is met de waarden die in een formule worden gebruikt. Bij de confidence-functie treedt deze fout op als een van de argumenten die aan de functie worden doorgegeven niet-numeriek is.

Controleer of alle argumenten (alpha, standaarddeviatie en steekproefomvang) numerieke waarden zijn, en als je naar cellen verwijst, zorg dan dat die numerieke data bevatten.

Natuurlijk kan de fout die je tegenkomt anders en uniek zijn — in dat geval is je beste optie om het Microsoft Answers-forum te raadplegen voor beantwoorde vragen. Je kunt in hetzelfde forum ook vragen stellen om jouw specifieke fouten op te lossen.

Conclusie

Deze tutorial heeft je geïntroduceerd in het concept van betrouwbaarheidsintervallen en het belang ervan in data-analyse. Vervolgens hebben we de syntaxis van de CONFIDENCE-functies in Excel besproken en wanneer je ze gebruikt, aan de hand van een stapsgewijze gids voor een waarschijnlijk realistisch scenario.

Functies kunnen in de toekomst worden bijgewerkt, dus de officiële Microsoft-documentatie is je beste informatiebron.

Tot slot: voor een complete leercurve om Excel in te zetten voor data-analytische skills, bekijk de Excel Fundamentals-track, waarin je begint als absolute beginner en eindigt met het bouwen van je eigen klantverloop-analyseproject in Excel.

We raden ook onze tutorial over basisformules in Excel en onze Excel-formules cheatsheet aan voor snelle Excel-referenties.