Meer informatie

Wanneer cumulatief = WAAR, bestaat het resultaat van de functie BINOMIALE.VERD(x, n, p, cumulatief) uit de kans op x of minder successen in n onafhankelijke Bernoulli-proeven. Elk van de proeven heeft een kans p op succes (en kans 1-p op mislukking). Wanneer cumulatief = ONWAAR, resulteert BINOMIALE.VERD in de kans op exact x successen.

Syntaxis

Parameters

• x is een niet-negatief geheel getal
• n is een positief geheel getal
• 0 < p < 1
• cumulatief is een logische variabele die wordt geëvalueerd als WAAR of ONWAAR

Voorbeeld van gebruik

Ga uit van de volgende aannamen:

• In honkbal betekent een slaggemiddelde van 0,300 dat de slagman bij elke slagbeurt (elke proef) een kans van 0,300 op succes (honkslag) heeft.
• Het aantal opeenvolgende slagbeurten zijn onafhankelijke Bernoulli-proeven.

Met de volgende tabel kunt u de kans berekenen dat een slagman met dat gemiddelde exact 0, 1, 2, ..., of 10 hits produceert in 10 proeven en de kans dat de slagman 0, 1 of minder, 2 of minder, ..., 9 of minder, of 10 of minder hits slaat in 10 proeven.

Als de slagman 50 honkslagen scoort in de eerste 200 proeven (gemiddelde van 0,250), heeft hij 100 hits nodig in de volgende 300 proeven om 150 honkslagen te krijgen en een gemiddelde van 0,300 over 500 proeven. Gebruik de volgende tabel om de kans te analyseren dat de slagman voldoende honkslagen produceert om zijn gemiddelde in stand te houden. Honkbalcommentators hebben het vaak over de 'wet der gemiddelden' wanneer ze zeggen dat de fans zich niet ongerust hoeven te maken over de prestaties van deze slagman als hij slechts 50 hits produceert in zijn eerste 200 proeven omdat zijn gemiddelde aan het einde van het seizoen 0,300 zal bedragen. Als de proeven echt onafhankelijk zouden zijn, en de slagman daadwerkelijk een kans op succes van 0,3 per willekeurige proef zou hebben, is deze redenering bedrieglijk omdat de resultaten van de eerste 200 proeven geen invloed hebben op het succes of de mislukking van de laatste 300 proeven.

Maak ter illustratie van de functie BINOMIALE.VERD een leeg Excel-werkblad, kopieer de volgende tabel, selecteer A1 in het lege Excel-werkblad en kies Plakken in het menu Bewerken zodat de cellen A1:C22 in het werkblad worden gevuld met de gegevens uit de volgende tabel.

aantal proeven	10
kans op succes	0,3
successen, x	P(exact x successen)	P(x of minder successen)
0	=BINOMIALE.VERD(A4;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A4;$B$1;$B$2;WAAR)
1	=BINOMIALE.VERD(A5;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A5;$B$1;$B$2;WAAR)
2	=BINOMIALE.VERD(A6;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A6;$B$1;$B$2;WAAR)
3	=BINOMIALE.VERD(A7;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A7;$B$1;$B$2;WAAR)
4	=BINOMIALE.VERD(A8;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A8;$B$1;$B$2;WAAR)
5	=BINOMIALE.VERD(A9;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A9;$B$1;$B$2;WAAR)
6	=BINOMIALE.VERD(A10;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A10;$B$1;$B$2;WAAR)
7	=BINOMIALE.VERD(A11;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A11;$B$1;$B$2;WAAR)
8	=BINOMIALE.VERD(A12;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A12;$B$1;$B$2;WAAR)
9	=BINOMIALE.VERD(A13;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A13;$B$1;$B$2;WAAR)
10	=BINOMIALE.VERD(A14;$B$1;$B$2;ONWAAR)	=BINOMIALE.VERD(A14;$B$1;$B$2;WAAR)
300 proeven, kans op succes 0,3:
successen, x	P(exact x successen)	P(x of minder successen)
89	=BINOMIALE.VERD(A18;300;0,3;ONWAAR)	=BINOMIALE.VERD(A18;300;0,3;WAAR)
90	=BINOMIALE.VERD(A19;300;0,3;ONWAAR)	=BINOMIALE.VERD(A19;300;0,3;WAAR)
99	=BINOMIALE.VERD(A20;300;0,3;ONWAAR)	=BINOMIALE.VERD(A20;300;0,3;WAAR)
100	=BINOMIALE.VERD(A21;300;0,3;ONWAAR)	=BINOMIALE.VERD(A21;300;0,3;WAAR)
101	=BINOMIALE.VERD(A22;300;0,3;ONWAAR)	=BINOMIALE.VERD(A22;300;0,3;WAAR)

Opmerking Nadat u deze tabel in het nieuwe Excel-werkblad hebt geplakt, klikt u op de knop Plakopties en vervolgens op Aanpassen aan opmaak van bestemming. Terwijl het geplakte bereik nog steeds is geselecteerd, wijst u Kolom aan in het menu Opmaak en klikt u op AutoAanpassen aan selectie. Misschien wilt u de cellen B4:C22 opmaken voor een consistente leesbaarheid (bijvoorbeeld als getallen met 5 decimalen).

Het bereik B4:B14 toont de kansen op exact x successen in 10 proeven. Het aantal successen is zeer waarschijnlijk 3. De kans op 0, 6, 7, 8, 9 of 10 successen is minder dan 0,05 en bedraagt opgeteld ongeveer 0,076. De kans op 1, 2, 3, 4 of 5 successen is dus ongeveer 1 — 0,076 = 0,924. Het bereik C4:C14 toont de kans op x of minder successen in 10 proeven. U kunt controleren of de invoer in een rij van kolom C gelijk is aan de som van alle invoer tot en met die rij in kolom B.

Het bereik B18:B20 toont aan dat in 300 proeven de kans op succes zeer waarschijnlijk 90 is. De kans op exact x successen neemt toe naarmate x oploopt tot 90, en neemt af wanneer x hoger wordt dan 90. De kans op 90 of minder successen is iets meer dan 50%, zoals u kunt zien in C20. De kans op 99 of minder successen is ongeveer 0,884. De kans op 100 of meer successen is dan ook slechts 11,6% (0,116 = 1 — 0,884).

Resultaten in oudere versies van Excel

Knusel (zie opmerking 1) heeft situaties beschreven waarbij BINOMIALE.VERD geen numeriek antwoord teruggeeft en in plaats daarvan #NUM! oplevert vanwege een numerieke overloop. Wanneer BINOMIALE.VERD numerieke antwoorden teruggeeft, zijn ze correct. BINOMIALE.VERD resulteert alleen in #NUM! wanneer het aantal proeven groter is dan of gelijk is aan 1030. De berekening levert geen problemen op als n < 1030. In de praktijk zijn dergelijke hoge waarden voor n onwaarschijnlijk. Bij een dergelijk groot aantal onafhankelijke proeven kan een gebruiker de binomiale verdeling benaderen met behulp van een normale verdeling (als n*p en n*(1-p) voldoende hoog zijn, bijvoorbeeld allebei groter dan 30) of met een Poisson-verdeling.

Opmerking 1 Knusel, L. 'On the Accuracy of Statistical Distributions in Microsoft Excel 97', Computational Statistics and Data Analysis (1998), 26: 375-377.

Voor de niet-cumulatieve casus gebruikt BINOMIALE.VERD(x, n, p, onwaar) de volgende formule:COMBINATIES is een Excel-functie die resulteert in het aantal combinaties van x items in een populatie van n items. COMBINATIES(n,x) wordt soms geformuleerd als nCx, en heet dan een 'gecombineerde coëfficiënt', of als 'n kiezen x'. Als u experimenteert met COMBINATIES door =COMBINATIES(1029;515) te typen in de ene cel en =COMBINATIES(1030;515) in een andere cel, resulteert de eerste cel in een astronomisch getal, 1,4298E+308, en de tweede cel in #NUM! omdat het resultaat nog groter is. De overloop van COMBINATIES veroorzaakt een overloop van BINOMIALE.VERD in oudere versies van Excel.

COMBINATIES is niet gewijzigd voor Excel 2003.

Resultaten in Excel 2003

Aangezien Microsoft heeft vastgesteld wanneer een overloop tot gevolg heeft dat BINOMIALE.VERD #NUM! als resultaat geeft en bekend is dat BINOMIALE.VERD geen problemen geeft als er geen overloop optreedt, heeft Microsoft een voorwaardelijk algoritme geïmplementeerd in Excel 2003. Het algoritme gebruikt BINOMIALE.VERD-code uit oudere versies van Excel (de rekenkundige formule die eerder in dit artikel is genoemd) wanneer n < 1030. Wanneer n >= 1030, gebruikt Excel 2003 het alternatieve algoritme dat verderop in dit artikel wordt beschreven. COMBINATIES veroorzaakt een overloop omdat de functie een astronomische waarde oplevert, maar p^x en (1-p)^(n-x) zijn beide oneindig klein. Als het mogelijk zou zijn beide waarden te vermenigvuldigen, zou het product een realistische kans tussen 0 en 1 vertegenwoordigen. Maar aangezien dit niet mogelijk is, voorkomt een alternatief algoritme de evaluatie van COMBINATIES. De aanpak van Microsoft bestaat eruit een ongeschaalde som te berekenen van alle kansen op exact x successen die later worden gebruikt voor schaling. Het algoritme berekent tevens een ongeschaalde waarde van de kans die u wilt dat BINOMIALE.VERD retourneert. Tot slot wordt de schalingsfactor gebruikt om een correcte BINOMIALE.VERD-waarde te retourneren. Het algoritme benut het feit dat de verhouding van opeenvolgende termen van de vorm COMBINATIES(n,k)*(p^k)*((1-p)^(n-k)) een eenvoudige vorm heeft. Het algoritme wordt verder verwerkt zoals in de pseudocode in de volgende stappen.

Stap 0: (Initialisatie). Stel de eigenschappen TotalUnscaledProbability en UnscaledResult in op 0. Stel de constante EssentiallyZero in op een heel klein getal, bijvoorbeeld 10^(-12).

Stap 1: Zoek n*p en rond naar beneden af naar het dichtstbijgelegen geheel getal, m. Het aantal successen in n proeven is zeer waarschijnlijk m of m+1. COMBINATIES(n,k)*(p^k)*((1-p)^(n-k)) wordt kleiner naarmate k afneemt van m naar m-1 naar m-2, enzovoort. Bovendien neemt COMBINATIES(n,k)*(p^k)*((1-p)^(n-k)) af naarmate k oploopt van m+1 naar m+2 naar m+3, enzovoort.Stap 2: Bereken de ongeschaalde kansen voor k > m:Stap 3: Bereken de ongeschaalde kansen voor k < m:Stap 4: Combineer de ongeschaalde resultaten:Hoewel deze methode alleen wordt gebruikt voor n >= 1030, kunt u de volgende toevoegingen aan het Excel-werkblad gebruiken om het algoritme handmatig te helpen uitvoeren wanneer u BINOMIALE.VERD(3; 10; 0,3; WAAR) berekent (in het honkbalvoorbeeld de kans op 3 of minder hits in 10 proeven voor een slagman met een gemiddelde van 0,300).

U kunt dit als volgt aantonen. Kopieer de onderstaande tabel, selecteer cel D1 in het Excel-werkblad dat u eerder hebt gemaakt en kies Plakken in het menu Bewerken zodat de cellen D1:E15 in het werkblad worden gevuld met de gegevens uit de volgende tabel.

=D5*(1-$B$2)*(A4+1)/($B$2*($B$1-A4))	=D4/$D$15
=D6*(1-$B$2)*(A5+1)/($B$2*($B$1-A5))	=D5/$D$15
1	=D6/$D$15
=D6*$B$2*($B$1-A7+1)/((1-$B$2)*A7)	=D7/$D$15
=D7*$B$2*($B$1-A8+1)/((1-$B$2)*A8)	=D8/$D$15
=D8*$B$2*($B$1-A9+1)/((1-$B$2)*A9)	=D9/$D$15
=D9*$B$2*($B$1-A10+1)/((1-$B$2)*A10)	=D10/$D$15
=D10*$B$2*($B$1-A11+1)/((1-$B$2)*A11)	=D11/$D$15
=D11*$B$2*($B$1-A12+1)/((1-$B$2)*A12)	=D12/$D$15
=D12*$B$2*($B$1-A13+1)/((1-$B$2)*A13)	=D13/$D$15
=D13*$B$2*($B$1-A14+1)/((1-$B$2)*A14)	=D14/$D$15
=SOM(D4:D14)

Kolom D bevat de ongeschaalde kansen. De 1 in cel D6 is het resultaat van stap 1 van het algoritme. Excel 2003 berekent de waarden in cellen D7, D8, ..., D14 in die volgorde in stap 2. De waarden in cellen D5 en D4 (in die volgorde) worden berekend in stap 3. De som van alle ongeschaalde kansen wordt weergegeven in D15.

Typ de volgende formule in een willekeurige lege cel om de kans op 3 of minder successen te berekenen: In het bovenstaande voorbeeld wordt stap 2 of 3 niet gestopt door EssentiallyZero. Als u echter BINOMIALE.VERD(550; 2000; 0,3; WAAR) wilt evalueren, kan stap 2 of stap 3 wel worden gestopt door EssentiallyZero. Een binomiale stochastische variabele met n = 2000 en p = 0,3 heeft een verdeling die wordt benaderd door de Normaal met gemiddelde 600 en standaarddeviatie WORTEL(2000*0,3*(1 — 0,3)) = WORTEL(420) = 20,5. 805 is dus 10 standaarddeviaties hoger dan het gemiddelde en 395 is 10 standaarddeviaties lager dan het gemiddelde. Afhankelijk van uw instelling van EssentiallyZero, zal EssentiallyZero stap 2 stoppen voordat u 805 bereikt en stap 3 stoppen voordat u 395 bereikt.

Conclusies

Onnauwkeurigheden in versies van Excel ouder dan Excel 2003 treden alleen op wanneer het aantal proeven groter is dan of gelijk is aan 1030. In deze gevallen retourneert BINOMIALE.VERD #NUM! in oudere versies van Excel omdat er overloop optreedt in een term die deel uitmaakt van een reeks termen die met elkaar worden vermenigvuldigd. Om dit probleem te verhelpen, wordt in Excel 2003 de alternatieve procedure gebruikt die eerder in dit artikel is beschreven, wanneer een dergelijke overloop zou optreden.

In oudere versies van Excel vertonen de functies CRIT.BINOM, HYPERGEO.VERD, NEG.BINOM.VERD en POISSON dezelfde symptomen. Deze functies retourneren ofwel de correcte numerieke resultaten ofwel #NUM! of #DEEL/0!. Nogmaals, problemen treden op als gevolg van overloop (of underflow). Het is eenvoudig vast te stellen wanneer en hoe deze problemen optreden. In Excel 2003 wordt een alternatief algoritme gebruikt dat vergelijkbaar is met het algoritme voor BINOMIALE.VERD om juiste antwoorden te retourneren in gevallen waarin oudere versies #NUM! als resultaat geven.

Als u meer informatie wilt over BINOMIALE.VERD, klikt u in het menu Help op Microsoft Excel Help, typt u binomiale.verd in het vak Zoeken naar en klikt u op Zoekactie starten.