Solutions to Excel and model circular references
  • Solutions to Excel and model circular references
Originally published: 21/04/2020 09:36
Publication number: ELQ-69081-1
View all versions & Certificate
certified

Solutions to Excel and model circular references

Circular references in Excel models: four solutions.

Description
PROBLEM
I have a circular reference in my model

SOLUTION
Circularity solutions
This paper gives working details of the four solutions to circular references in Excel models.

CIRCULAR REFERENCES
A circular reference is when a formula either directly references itself or indirectly references other cells that reference it.

THERE ARE FOUR SOLUTIONS
1/ Allow the circular reference and turn ‘iterative calculations’ on in Calculation options within Excel Options – this is an exact solution
2/ Carry out a modelling approximation to avoid the circular reference by calculating off the previous period value or balance – this is an approximate solution
3/ Rearrange the formula – this is an exact solution
4/ Use a macro to break the circular reference – this is an exact solution

EXAMPLE CIRCULAR REFERENCE PROBLEM
We consider the interest on cash problem within a financial model. The interest on cash is part of the Cash carried forward calculation that is itself part of the interest on cash calculation. This calculation regularly gives rise to a circular reference in Excel models.
This paper discusses, in detail, the four solutions to this problem.

INCLUDED IN THIS BEST PRACTICE
1/ PDF paper detailing the four solutions
2/ XLSM file with problem and 4 solutions
3/ VBA code for 4th solution

This Best Practice includes
1x PDF paper detailing the four solutions, 1x XLSM file with problem and 4 solutions

Dominic Robertson offers you this Best Practice for free!

download for free

Add to bookmarks

Discuss

Further information

To provide practical & working solutions to circular references in Excel models.

This tool is best for analysts and modellers who wish to add to their practical and theoretical modelling knowledge.
This tool is best for analysts and modellers who wish to add only best practice modelling knowledge.

Reviews

  • Be the first to review this Downloadable Tool

    Write a review


keyboard_arrow_leftkeyboard_arrow_right

Any questions on Solutions To Excel And Model Circular References?

The user community and author are here to help. Go ahead!


0.0 / 5 (0 votes)

please wait...