# Circular Reference Error with Multiple Expiry Dates

1. ## Circular Reference Error with Multiple Expiry Dates

Evening All,

I'm hoping that someone will be able to enlighten me as to the solution for a problem that I am having with circular references.

Basically what I am trying to do is to create a financial model of my car in Excel, and part of this is having Excel calculate when the estimated date that need to replace the car. There are two factors which dictate the end of life date, which are the mileage limit and age limit, and whichever of the two values are reached first should then trigger the change of state to 'sold'. The problem comes when trying to tell Excel to choose whichever of the two expiry dates occurs first, which triggers a circular reference warning whenever I try to write a formula to integrate the two.

I think that part of the problem is that the Mileage expiry trigger date uses an INDEX/MATCH formula which looks up the date on which the mileage expiry value is met and then returns the date from this column, yet if the age expiry occurs before this then the mileage value will never be met as it is then set to 0. The other consideration is that the mileage data is not linear (as the 'Event' field value depends on one-off events), so it isn't possible to extrapolate the total mileage value to estimate the mileage expiry date either.

I've created a .xlsx file with a cut-down version of the car object to demonstrate the problem that I am having:

If anyone would be able to suggest a solution it would be greatly appreciated!

Kind regards,

2. ## Re: Circular Reference Error with Multiple Expiry Dates

I'm not sure I've understood exactly how your sheet works, but generally circular reference problems can be solved by turning on iterative calculations.
Iterative calculations let Excel calculate the cells one by one and once again until they are in balance or number of iterations determined is done instead of solving all together (by default)

To turn this on:
File -> Options -> Formulas -> Check Enable iterative calculations and set the number of iterations you want

I hope this helps :D

3. ## Re: Circular Reference Error with Multiple Expiry Dates

Hi M.Ali,

Thank you for your reply, I had tried playing with iterative calculations however could never get it to work!

I have actually been able to resolve this one now with some lateral thinking, only took me 12 attempts in the end!

Many thanks,

There are currently 1 users browsing this thread. (0 members and 1 guests)