Trouver le nombre manquant

Toujours dans les échanges entre fêlés d’Excel, j’ai encore découvert un challenge intéressant qui a provoqué de multiples propositions.

Vous avez en A2:A8 une série de valeurs ayant la propriété suivante : ce sont tous des nombres entiers, formant une séquence non ordonnée dans laquelle un seul nombre est manquant. Dans notre exemple, on voit tout de suite qu’il s’agit du nombre 4.

Pour que vous compreniez mieux les formules proposées, il faut connaître la fameuse formule donnant la somme des valeurs de 1 à n :  
1 + 2  + … + (n-1) + n = n * (n+1) / 2

Vous trouvez en B2 à B5 les formules proposées par quatre personnes, avec le nom de l’auteur à droite.

La formule que je propose est celle qui m’est venue immédiatement à l’esprit : elle est intuitive et applique tout simplement, sans la moindre fioriture, la fameuse formule.

Roberto Mensa propose une formule plus courte, mais dont la compréhension demande un peu plus de travail…

Henk de Jong, en utilisant lui aussi la fonction lignes(), parvient à réduire encore la taille de la formule.

Bruce McPherson, enfin, trouve la formule qui est à la fois la plus courte et la plus originale. Bravo !

Flexibilité et robustesse des formules

Mais la compacité d’une formule n’est pas tout. Il est souhaitable qu’une formule soit flexible et/ou robuste.

Qu’advient-il donc des quatre formules si l’on insère par exemple deux lignes blanches entre la ligne 7 et la ligne 8 ?

Dans ce cas, seules ma formule et celle de McPherson continuent à fournir le bon résultat !

Qu’advient-il enfin si, dans une des deux nouvelles cellules de la liste, nous entrons une valeur faisant doublon avec une autre valeur de la liste ?

Dans ce cas, aucune des quatre formules ne résiste.

Mais un autre membre du groupe, Elias Estrada, a trouvé une formule – certes deux fois plus longue, et à valider avec [Ctrl]-[Maj]-[Entrée] – qui résiste même à cette agression :

=min(si(nb.si(A2:A8;ligne(indirect(min(A2:A8)&":"&max(A2:A8))))=0;ligne(indirect(min(A2:A8)&":"&max(A2:A8)))))

Impressionnant, n’est-ce pas !

Cela nous prouve, une fois de plus, qu’il y a de multiples façons de modéliser dans Excel !