LOI.BINOMIALE.NEG.N répond à la question inverse de la binomiale classique : au lieu de fixer le nombre d'essais, tu fixes le nombre de succès visés, et tu calcules la probabilité d'avoir subi un certain nombre d'échecs avant d'atteindre cet objectif. C'est la différence fondamentale avec LOI.BINOMIALE.N.
En pratique, c'est elle qui modélise combien de prospects contacter avant de décrocher un nombre cible de ventes, combien de candidats interviewer avant de pourvoir plusieurs postes, ou combien de pièces tester avant de trouver le quota requis de pièces conformes. Partout où ton objectif de succès est fixe et que c'est le nombre d'essais qui varie, c'est la distribution binomiale négative qu'il faut utiliser.
Syntaxe de la fonction LOI.BINOMIALE.NEG.N
=LOI.BINOMIALE.NEG.N(nb_echecs; nb_succes; probabilite_succes; cumulative)Comprendre chaque paramètre de la fonction LOI.BINOMIALE.NEG.N
Les quatre arguments se suivent dans un ordre imposé : d'abord le nombre d'échecs que tu explores, puis le nombre de succès visés, ensuite la probabilité de succès par essai, et enfin le booléen cumulative. Aucun n'est facultatif, ils sont tous obligatoires.
Garde en tête que c'est nb_succes qui fixe ta cible et nb_echecs qui varie : c'est exactement l'inverse de la binomiale classique, et inverser ces deux-là est le piège classique de cette fonction.
nb_echecs
: le nombre d'échecs pour lequel tu calcules la probabilitéCe doit être un entier positif ou nul. C'est la variable dont tu explores la distribution : combien d'échecs peut-on avoir avant d'atteindre l'objectif de succès ?
En faisant varier ce paramètre de 0 à une valeur élevée, tu construis la distribution complète et tu identifies les scénarios réalistes pour ton dimensionnement.
nb_succes
: le nombre de succès requis avant d'arrêterC'est ton objectif fixe, la cible à atteindre. Par exemple, si tu veux 5 ventes, nb_succes = 5. La valeur doit être un entier strictement positif (supérieur ou égal à 1).
C'est ce paramètre qui distingue la binomiale négative de la binomiale classique : ici c'est le nombre de succès qui est fixé, pas le nombre d'essais.
Astuce : Quand nb_succes = 1, tu obtiens la loi géométrique : la distribution du nombre d'échecs avant le premier succès. Exemple classique : combien d'appels avant la première vente ?
probabilite_succes
: la probabilité de succès à chaque essai indépendant, entre 0 et 1 (exclus)Par exemple, si ton taux de conversion est de 25 %, utilise 0,25. Cette probabilité reste constante pour tous les essais : c'est l'hypothèse centrale de la distribution.
Si la probabilité varie d'un essai à l'autre (saisonnalité, qualité du lead), la binomiale négative ne s'applique plus directement.
Attention : La probabilité doit être strictement supérieure à 0 et inférieure à 1. Une valeur de 0 ou 1 exactement génère une erreur #NOMBRE!.
cumulative
: indique si tu veux la probabilité exacte ou cumuléeFAUX retourne P(X = nb_echecs), la probabilité d'avoir exactement ce nombre d'échecs. VRAI retourne P(X <= nb_echecs), la probabilité d'avoir au plus ce nombre d'échecs.
Le mode cumulatif est généralement plus utile pour le dimensionnement : « quelle est la probabilité de ne pas dépasser X échecs ? » correspond à un niveau de service ou de confiance que tu peux directement piloter.
Exemples pratiques pas à pas
Responsable commercial : dimensionner un pipeline de prospection
Tu es responsable commercial et tu dois atteindre 8 nouvelles signatures de contrat ce mois-ci. Ton taux de conversion est de 20 % sur les prospects qualifiés. Tu veux savoir combien de prospects contacter pour avoir une probabilité suffisante d'atteindre cet objectif.
En faisant varier nb_echecs et en lisant les probabilités cumulées, tu construis une table de décision : avec 25 échecs (soit 33 prospects au total), tu n'as que 28 % de chances d'y arriver. Avec 40 échecs (48 prospects), tu montes à 76 %. Pour 90 % de confiance, prévois environ 48 échecs (56 prospects au total) : tu ajustes ton budget de prospection en conséquence.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Échecs max | Succès visés | Taux conversion | Cumulative | Résultat | Interprétation |
| 2 | 25 | 8 | 0,20 | VRAI | 0,2836 | 28 % de chances avec 25 échecs max |
| 3 | 30 | 8 | 0,20 | VRAI | 0,4482 | 45 % avec 30 échecs max |
| 4 | 40 | 8 | 0,20 | VRAI | 0,7560 | 76 % avec 40 échecs max |
=LOI.BINOMIALE.NEG.N(25; 8; 0,2; VRAI)Astuce de pro : Pour trouver le nombre d'échecs garantissant un niveau de confiance précis (ex. 90 %), fais varier nb_echecs dans un tableau ou utilise le Solveur Excel avec une contrainte sur le résultat.
Responsable RH : planifier un recrutement de profils rares
Tu es responsable RH et tu dois recruter 3 développeurs seniors. Seulement 15 % des candidats atteignent le niveau technique requis. Tu veux estimer combien de candidats tu devras interviewer pour être sûr à 90 % de pourvoir les 3 postes.
La table de résultats montre que pour avoir 90 % de chances de trouver tes 3 candidats, il faut prévoir 30 rejets, soit 33 entretiens au total (30 + 3). Tu planifies ainsi tes ressources RH : temps d'entretien, volume de candidatures à sourcer, délai du processus.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Scénario | Formule | Résultat | Interprétation |
| 2 | P(exactement 10 rejets) | =LOI.BINOMIALE.NEG.N(10;3;0,15;FAUX) | 0,0401 | 4 % de chances |
| 3 | P(au plus 15 rejets) | =LOI.BINOMIALE.NEG.N(15;3;0,15;VRAI) | 0,4413 | 44 % de chances |
| 4 | P(au plus 20 rejets) | =LOI.BINOMIALE.NEG.N(20;3;0,15;VRAI) | 0,6778 | 68 % de chances |
| 5 | P(au plus 30 rejets) | =LOI.BINOMIALE.NEG.N(30;3;0,15;VRAI) | 0,9031 | 90 % de chances |
=LOI.BINOMIALE.NEG.N(30; 3; 0,15; VRAI)Responsable qualité : contrôle d'un lot de production
Tu es responsable qualité et tu supervises une ligne de production avec 5 % de défauts (taux de conformité 95 %). Pour valider un lot, tu dois trouver 10 pièces conformes. Tu veux modéliser combien de pièces défectueuses tu risques de rencontrer en cours de contrôle.
Les probabilités cumulées te montrent qu'avec 95 % de conformité, tu trouveras tes 10 pièces conformes en inspectant 10 à 13 pièces dans 99 % des cas. Ce niveau de confiance est largement suffisant pour valider ton protocole de contrôle sans allonger excessivement le temps d'inspection.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Nb défauts | P(X = nb) | P(X <= nb) | Interprétation |
| 2 | 0 | 0,5987 | 0,5987 | 60 % de trouver 10 conformes sans défaut |
| 3 | 1 | 0,3152 | 0,9139 | 91 % avec au plus 1 défaut |
| 4 | 2 | 0,0746 | 0,9885 | 99 % avec au plus 2 défauts |
| 5 | 3 | 0,0099 | 0,9984 | 99,8 % avec au plus 3 défauts |
=LOI.BINOMIALE.NEG.N(1; 10; 0,95; VRAI)Envie de t'entraîner sur de vrais exercices Excel ?
M'entraîner
Les erreurs fréquentes avec la fonction LOI.BINOMIALE.NEG.N
Deux blocages reviennent ici, et ils n'ont rien à voir l'un avec l'autre. Le #NOMBRE! se déclenche dès qu'un paramètre sort de ses bornes : un nb_echecs négatif, un nb_succes inférieur à 1, ou une probabilité égale pile à 0 ou 1 au lieu de rester dans l'intervalle ouvert. Le #VALEUR!, lui, signale qu'un argument n'est tout simplement pas un nombre, typiquement un cumulative écrit "VRAI" en texte plutôt qu'en booléen.
Le troisième cas est plus sournois car Excel ne renvoie aucune erreur : tu confonds cette fonction avec LOI.BINOMIALE.N et le résultat est silencieusement faux. La question qui tranche : est-ce le nombre d'essais ou le nombre de succès que tu as fixé ?
Erreur #VALEUR! sur un argument
Cette erreur apparaît quand un argument n'est pas numérique : cellule vide, cellule contenant du texte, ou paramètre cumulative passé sous forme de chaîne plutôt que comme valeur booléenne.
Solution : Vérifie que toutes les cellules référencées contiennent des nombres et que le paramètre cumulative est bien VRAI ou FAUX (pas "VRAI" en texte). Utilise ESTNUM(cellule) pour diagnostiquer.
Erreur #NOMBRE! sur les paramètres
Cette erreur survient quand les paramètres sont hors limites : nb_echecs < 0, nb_succes < 1, ou probabilite_succes hors de l'intervalle ouvert ]0 ; 1[.
Solution : Assure-toi que nb_echecs >= 0, nb_succes >= 1, et que la probabilité est strictement entre 0 et 1. Vérifie que tes cellules source ne contiennent pas de valeurs calculées hors limites.
Confusion avec la binomiale classique
Erreur conceptuelle fréquente : utiliser LOI.BINOMIALE.NEG.N quand c'est LOI.BINOMIALE.N qui convient, ou inversement. La distinction clé : est-ce le nombre d'essais ou le nombre de succès qui est fixé ?
Solution : Applique la règle suivante : si tu fixes le nombre d'essais et cherches la probabilité d'un certain nombre de succès, utilise LOI.BINOMIALE.N. Si tu fixes le nombre de succès et cherches la probabilité d'un certain nombre d'échecs, utilise LOI.BINOMIALE.NEG.N.
Questions fréquentes sur la fonction LOI.BINOMIALE.NEG.N
Quelle est la différence entre LOI.BINOMIALE.NEG.N et LOI.BINOMIALE.N ?
La différence est dans la question posée. LOI.BINOMIALE.N répond à « en n essais, quelle est la probabilité d'avoir k succès ? » (le nombre d'essais est fixé). LOI.BINOMIALE.NEG.N répond à « pour obtenir k succès, quelle est la probabilité d'avoir eu f échecs avant ? » (le nombre de succès est fixé).
En résumé : essais fixes = binomiale. Succès fixes = binomiale négative.
Comment interpréter le paramètre cumulative ?
Avec FAUX, tu obtiens P(X = nb_echecs), la probabilité exacte d'avoir précisément ce nombre d'échecs. Avec VRAI, tu obtiens P(X <= nb_echecs), la probabilité d'avoir au plus ce nombre d'échecs.
Le mode cumulatif est souvent plus utile pour le dimensionnement : « quelle est la probabilité de ne pas dépasser X échecs ? » te permet de fixer un niveau de confiance (ex. 90 %) et de lire directement combien d'essais prévoir.
Quand utiliser la binomiale négative plutôt que la loi de Poisson ?
La loi de Poisson suppose que la variance est égale à la moyenne. La binomiale négative permet de modéliser la surdispersion, c'est-à-dire les cas où la variance est supérieure à la moyenne.
Pour des données de comptage avec forte variabilité (visites clients, sinistres, absentéisme), la binomiale négative est souvent plus adaptée. Elle est aussi plus appropriée quand tu peux explicitement modéliser une probabilité de succès à chaque essai.
Qu'est-ce que la loi géométrique ?
La loi géométrique est un cas particulier de la binomiale négative avec nb_succes = 1. Elle modélise le nombre d'échecs avant le premier succès.
Exemple concret : combien d'appels avant ta première vente ? Si chaque appel a 20 % de chances d'aboutir, =LOI.BINOMIALE.NEG.N(f; 1; 0,2; FAUX) te donne la probabilité d'avoir exactement f échecs avant le premier succès.
Comment dimensionner un pipeline commercial avec cette fonction ?
Si chaque appel a 30 % de chances de succès et que tu dois conclure 10 ventes, construis un tableau en faisant varier nb_echecs de 0 à une valeur élevée et calcule =LOI.BINOMIALE.NEG.N(f; 10; 0,3; VRAI) pour chaque ligne.
Trouve la valeur de f où la probabilité cumulée atteint ton niveau de confiance souhaité (ex. 95 %). Ajoute nb_succes pour obtenir le nombre total d'appels à prévoir. Tu as ton objectif de pipeline.
La fonction tient-elle compte de la saisonnalité ou des variations de taux ?
Non. La binomiale négative suppose une probabilité de succès constante à chaque essai. Si ton taux de conversion varie selon la saison, le canal ou le profil du prospect, la distribution ne s'applique pas directement.
Dans ce cas, tu peux découper ton analyse par segment (un calcul par période ou par canal) et agréger les résultats, ou utiliser une simulation Monte-Carlo pour modéliser la variabilité de la probabilité.
Pour aller plus loin
Bloqué sur une formule Excel ?
Pose ta question à notre assistant Excel IA, il te sort la bonne formule en quelques secondes.
Essayer l'assistant IAGratuit · 10 questions par mois