Les dés d’Excel sont pipés !

Voici ce que vous avez peut-être obtenu en créant le modèle que je vous ai décrit dans le dernier message et en cliquant sur le bouton « Simuler » pour lancer la simulation :

Manifestement, Excel utilise des dés pipés puisque nous avons obtenu 10 fois le « 5 ». Pourquoi donc ?

La clé se trouve dans la formule de tirage du dé, en B5 : =si(Drapeau=0;"";ent(6*alea()+1)).

Cette formule tire un nouveau dé si le drapeau n’est pas nul. Or, dans la macro lançant la simulation, nous mettons le drapeau à « 1 » puis nous lançons dix itérations. Le problème vient de ce qu'Excel recalcule seulement – à chaque itération – ce qui lui semble pouvoir changer !

Comme le drapeau n’a pas bougé entre la première itération et la seconde, Excel ne recalcule pas le si() à la seconde itération, ni bien entendu lors des suivantes ! C’est pour cela que l’on obtient 10 fois le même dé…

La solution est toute simple : il suffit de faire dépendre le si() d’une cellule qui change de valeur à chaque itération, par exemple le compteur en B3, avec la formule : =si(B3=0;"";ent(6*alea()+1)).

Vous obtenez alors un résultat tel que le suivant :

Certes, il y a du progrès, mais nous sommes encore loin du compte avec le dixième dé perdu et une fréquence cumulée de 91 % au lieu de 100 %. Nous verrons dans le prochain message comment résoudre ces deux problèmes résiduels…