Une énigme résolue ...

Voici le tableau que j’ai construit pour résoudre l’énigme présentée dans l’article précédent.

J’ai pris comme point de départ en A3 que Calixte avait actuellement une chance sur deux (50%) de se réveiller la nuit. J’en ai déduit – cf. les formules reproduites dans le bloc encadré de bleu – les probabilités de réveil chaque année de Samuel (colonne B) et de Calixte (colonne C) et, par voie de conséquence, la probabilité en colonne D de ne pas être réveillé la nuit. L’objectif à atteindre (6 nuits sur 7) étant affiché en F3.

Etape 1 – Calcul de la probabilité de réveil de Calixte

La première étape consiste à remplacer A3 par la probabilité de réveil qui aboutit à la sutuation actuelle, c’est-à-dire 1 chance sur 3 de non-réveil. Utilisons donc la valeur cible (via Données – Analyse scénarios) pour cela. Nous obtenons un résultat de 33,41%, ce qui n’est pas terrible. Recommençaons avec le solveur et, là, nous obtenons le résultat exact.

Remarque 1 – La valeur cible du solveur est de bien meilleure qualité que la valeur cible standard. N’hésitez pas à passer par le solveur pour calculer une valeur cible !

Nous constatons à présent que la probabilité de non-réveil des parents est à 80,57% dans 2 ans et à 90,06% dans 3 ans. Notre objectif de 85,71% sera donc atteint dans un peu plus de 2 ans.

Etape 2 – Création de la représentation graphique

Sélectionnons à présent – avec la touche [Ctrl] enfoncée – les blocs A4:A6 et D4:D6 puis insérons un graphe en nuage de points (soit en X/Y). Puis modifions le type de graphe en ligne et ajoutons-lui une courbe de tendance logarithmique, en demandant l’affichage de l’équation.

Remarque 2 – Nous avons choisi une croissance logarithmique car cela correspond bien à la division par 2 chaque année.

Nous obtenons alors le graphe de la copie d’écran ci-dessous. Il ne reste plus qu’à entrer 2 en B9 – une première estimation (grossière) du nombre d’années à attendre – et la formule =0,2472*ln(B9)+0,6311 en C9.

Etape 3 – Calcul de la solution exacte

Il ne nous reste plus qu’à trouver la valeur de x (en B9) qui nous donne le résultat attendu (85,71%) de y (en C9). Encore un boulot pour la valeur cible du solveur d’Excel qui trouve le résultat final : 2,495 années.

C’est donc quand Calixte aura 3,5 ans que les parents pourront dormir tranquilles 6 jours sur 7 !

En guise de conclusion

Bon, j’ai bien pris mon temps pour tout vous expliquer en détail, mais voici le déroulé des opérations une fois que l’on a créé le tableau et les formules de départ (cf. la première copie d’écran) :

• Calcul par la valeur cible de la valeur en B3 qui nous donne un résultat de 1/3 en D3
• Sélection de A4:A6 et D4:D6, graphe en X/Y, puis en ligne
• Ajout d’une courbe d’ajustement logarithmique avec affichage de l’équation
• Entrée de cette formule et C9 et calcul par valeur cible du B9 qui donnera 85,71% en C9