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

Désolé, mais j’étais chargé ces temps-ci, entre les trois développements Excel que j’assure en ce moment pour des entreprises et ma présence ces deux derniers jours aux TechDays de Microsoft, dans lequel j’ai répondu aux questions du public dans le thème  « Ask The Expert » sur le stand Office ; je vous retrouve donc après un sevrage de six jours...

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

Pour plus de clarté, j’ai reproduit en colonne C ce qui avait été saisi dans la colonne B…

Et patatras ! Quand je cherche à valider la formule de sommation en B4, Excel m’annonce qu’il ne peut pas calculer la formule car il y a une référence circulaire :

Il s’agit d’une sécurité normale car, le plus souvent, les références circulaires sont tout à fait involontaires, il s’agit d’erreurs de conception qu’Excel signale avec raison.

Il se peut cependant que – c’est d’ailleurs 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’appeler la commande Options d’Excel, onglet « Formules », puis de cocher la case « Activer le calcul itératif », comme nous l’avons fait 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 bientôt comment tirer profit des deux réglages suivants, le nombre maximal d’itérations et l’écart maximal.