Création d’une référence circulaire

Nous allons aujourd’hui nous attaquer à un problème qui vous a sûrement intrigué un jour ou l’autre : l’apparition d’une référence circulaire !

Réalisons un petit modèle dans lequel nous entrons un prix d’achat en B2, nous déclarons en B4 que nous allons faire une marge de 30% du prix de vente et, en B5, nous demandons la somme grâce à l’outil de sommation.

Et patatras ! Excel nous annonce qu’il ne peut pas calculer la formule car il y a une référence circulaire :

Remarque – Le message d’Excel est inexact et, de plus, moins clair qu’il ne l’était dans les versions antérieures du logiciel : en effet, Excel ne semble plus envisager que vous ayez pu construire cette référence circulaire volontairement !

Il est vrai que, bien souvent, les références circulaires sont tout à fait involontaires. Il s’agit en général d’erreurs de conception qu’Excel signale avec raison.

Mais il se peut cependant – c’est le cas ici – qu’il ne s’agisse pas d’une erreur mais que nous souhaitions effectivement calculer un résultat par convergence. C’est par exemple indispensable si vous construisez un modèle de calcul d’ISF (Impôt Sur la Fortune) : en effet, dans ce cas, on peut déduire, pour calculer la base imposable, le montant de l’ISF à payer qui dépend de cette même base imposable ! Encore une invention d’énarque...

Pour valider le calcul en références circulaires, il vous suffit d’utiliser la commande Fichier – Options – Formules, et de cocher « Activer le calcul itératif », comme nous le voyons dans la copie d’écran ci-dessous.

Le résultat final de 142,86 € apparaît alors aussitôt dans la cellule B4 !

Et voilà, ce n’est pas plus compliqué que cela...

Nous verrons dans le prochain article comment tirer profit des deux réglages suivants, le nombre maximal d’itérations et l’écart maximal.