+ Reply to Thread
Results 1 to 3 of 3

Circular Reference Error with Multiple Expiry Dates

  1. #1
    Registered User
    Join Date
    08-07-2017
    Location
    UK
    MS-Off Ver
    2013 (Mac)
    Posts
    2

    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
    Last edited by finaltoland; 09-10-2017 at 08:32 AM.

  2. #2
    Registered User
    Join Date
    09-08-2017
    Location
    Cairo
    MS-Off Ver
    2016
    Posts
    4

    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. #3
    Registered User
    Join Date
    08-07-2017
    Location
    UK
    MS-Off Ver
    2013 (Mac)
    Posts
    2

    Lightbulb 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,



    Adrian

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Circular reference error
    By Furin Hoang in forum Excel General
    Replies: 10
    Last Post: 06-06-2017, 11:21 AM
  2. Circular Reference Error
    By jhclaws in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-10-2013, 05:52 AM
  3. Circular reference error
    By Cjax in forum Excel General
    Replies: 5
    Last Post: 03-24-2011, 10:10 PM
  4. Circular Reference Error
    By nevi in forum Excel General
    Replies: 3
    Last Post: 07-05-2010, 04:51 PM
  5. Circular reference error
    By Riddling Lynx in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2008, 11:43 PM
  6. Circular Reference error...
    By Regnab in forum Excel General
    Replies: 0
    Last Post: 05-22-2006, 08:25 AM
  7. [SOLVED] Circular reference error
    By goss9394@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2005, 11:01 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1