Qu'est-ce que la fonction LOI.
Tu t'en sers quand ton objectif est fixé en nombre de succès à obtenir (un quota de ventes, de postes à pourvoir, de pièces conformes) et que tu veux savoir combien d'échecs tu dois t'attendre à encaisser en chemin, pour dimensionner ton effort en amont plutôt que de le découvrir en cours de route.
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
=LOI.BINOMIALE.NEG.N(nb_echecs; nb_succes; probabilite_succes; cumulative)Clique sur un argument pour aller à son explication.
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.
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
Comment calculer la probabilité d'un nombre de refus avant d'atteindre un objectif de ventes avec la fonction LOI.BINOMIALE.NEG.N
L'objectif du mois est arrêté à 8 signatures et le taux de conversion de l'équipe tourne autour de 20 % sur les prospects qualifiés. Le fichier de prospection, lui, est dimensionné au doigt mouillé, et la question qui fâche arrive toujours en fin de mois : le volume prévu était-il seulement suffisant ? Ici, ce n'est pas le nombre de contacts qui est fixé mais le nombre de ventes à obtenir, et c'est le nombre de refus encaissés en chemin qui varie. La fonction LOI.BINOMIALE.NEG.N donne la probabilité de tenir l'objectif sans dépasser un nombre de refus donné.
- 1Dans une cellule, écris
=LOI.BINOMIALE.NEG.N(. - 2En 1er argument : clique sur la cellule du nombre de refus que tu explores,
B2, qui affiche25pour le scénarioS-01. C'est la seule référence qui bouge à la recopie, parce qu'elle porte la variable de cette loi, celle dont tu ignores la valeur d'avance. - 3En 2ᵉ argument : saisis ton objectif de ventes,
8, écrit en dur parce que c'est une décision et non une observation. Ne l'intervertis pas avec le refus du premier argument, car ici la cible est fixe et ce sont les échecs qui varient, exactement l'inverse de la binomiale ordinaire. - 4En 3ᵉ argument : saisis le taux de conversion par prospect,
0,2pour les20 %de la colonneTaux. Il s'écrit en décimal et doit rester strictement entre0et1, ces deux bornes étant exclues. - 5En 4ᵉ argument : saisis
VRAIpour additionner tous les scénarios jusqu'à25refus inclus. C'est ce qui répond à « au plus25refus » plutôt qu'à « exactement25refus », et c'est aussi ce qui sépare cette fonction de son aînée, restée à trois arguments et bloquée sur le mode exact. - 6Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas pour obtenir la probabilité cumulée de chaque scénario.
=LOI.BINOMIALE.NEG.N(B2; 8; 0,2; VRAI)25 refus avant de décrocher les 8 ventes n'arrive que dans 33,4 % des cas : deux fois sur trois, il en faudra davantage. La colonne recopiée donne le calendrier complet, la barre des 50 % tombant à 30 refus et celle des 77,1 % à 40. Le mode cumulé est ce qui rend ce tableau lisible, car la probabilité d'un nombre de refus exact plafonne autour de 3,3 % quel que soit le nombre visé, une distribution bien trop plate pour décider quoi que ce soit.
Comment convertir un nombre de prospects à contacter en probabilité d'atteindre l'objectif avec la fonction LOI.BINOMIALE.NEG.N
La table des refus est juste mais personne ne l'utilise, parce qu'un fichier de prospection ne se commande pas en refus : il se commande en nombre de contacts. Traduire mentalement à chaque lecture est le meilleur moyen de se tromper d'une unité, et la conversation avec la direction s'enlise dès qu'il faut expliquer pourquoi la colonne affiche 25 quand le fichier en compte 33. Autant faire porter la soustraction par la formule et saisir directement le nombre de prospects. La fonction LOI.BINOMIALE.NEG.N reçoit alors un calcul plutôt qu'une cellule.
- 1Dans une cellule, écris
=LOI.BINOMIALE.NEG.N(. - 2En 1er argument : écris l'expression
A2-8plutôt qu'une cellule brute. Un prospect contacté finit soit en vente, soit en refus, donc les refus valent le total moins les8ventes visées. PointerA2seul décalerait toute la colonne de huit lignes. - 3En 2ᵉ argument : saisis l'objectif de ventes,
8. C'est le même8que celui de la soustraction, et ce lien est logique et non décoratif, si bien que changer l'objectif oblige à toucher les deux endroits. - 4En 3ᵉ argument : saisis le taux de conversion de l'équipe,
0,2, inchangé d'une ligne à l'autre. - 5En 4ᵉ argument : saisis
VRAIpour lire la colonne comme un niveau de confiance. C'est ce mode qui répond à la seule question qui compte vraiment, celle du volume de fichier à commander. - 6Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas pour convertir chaque volume de prospects en probabilité d'atteinte.
=LOI.BINOMIALE.NEG.N(A2-8; 8; 0,2; VRAI)40 prospects donnent un peu plus d'une chance sur deux avec 56,3 %, il en faut 56 pour approcher les 90 %, et les 60 du fichier actuel plafonnent à 93,3 %. La courbe s'aplatit nettement en haut, chaque point de confiance gagné au-delà de 90 % coûtant beaucoup plus de contacts que le précédent, ce qui est précisément l'argument à sortir quand on te demande de garantir l'objectif à 100 %.
Comment gérer l'erreur #NOMBRE! d'un taux de conversion invalide avec les fonctions LOI.BINOMIALE.NEG.N et SIERREUR
Les taux de conversion sont désormais saisis canal par canal, pour que chaque responsable mette à jour le sien sans toucher aux formules. Le revers est arrivé dès la première semaine : un canal encore vide a été renseigné à 0, un autre à 1,2 par quelqu'un qui pensait en pourcentage, et toute la colonne s'est mise à afficher un code d'erreur que personne n'a su rattacher à sa cellule. La fonction ne pardonne aucune probabilité en dehors de l'intervalle ouvert, et c'est heureux, car un taux nul ou certain ne veut rien dire ici. La fonction SIERREUR intercepte le code et le remplace par un message qui désigne la bonne cellule.
- 1Dans une cellule, écris
=SIERREUR(. - 2En 1er argument : écris le calcul à tenter et referme-le,
LOI.BINOMIALE.NEG.N(25; 8; B2; VRAI). La fonction part sur25refus,8ventes visées et le mode cumulé, mais son taux est lu dansB2et doit rester strictement entre0et1. C'est cette lecture libre qui peut faire échouer le calcul et renvoyer#NOMBRE!. - 3En 2ᵉ argument : saisis le texte de repli,
"Taux invalide". Il s'affiche à la place du code d'erreur et désigne la cellule à reprendre, là où une cellule vide laisserait croire que le canal n'a jamais été calculé. - 4Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas pour que chaque canal affiche sa probabilité ou le message d'alerte.
=SIERREUR(LOI.BINOMIALE.NEG.N(25; 8; B2; VRAI); "Taux invalide")0 du canal partenariat semble innocent et veut dire « aucune vente pour l'instant », sauf qu'un taux nul rend l'objectif mathématiquement inatteignable, d'où le #NOMBRE! que la fonction SIERREUR remplace ici par un message lisible. Le 1,2 est un 120 % tapé trop vite et échoue pour la raison inverse. Le reste du tableau chiffre au passage ce que vaut le canal : à 25 refus, un salon à 30 % tient l'objectif dans 81,8 % des cas contre 11,1 % pour l'appel à froid.
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
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.
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.
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.
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.
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.
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
Continue sur ta lancée après la fonction LOI.BINOMIALE.NEG.N : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
