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,



Adrian