+ Reply to Thread
Results 1 to 5 of 5

Excel diverges during iteration

  1. #1
    Registered User
    Join Date
    12-06-2019
    Location
    berlin
    MS-Off Ver
    MS office 7
    Posts
    3

    Excel diverges during iteration

    Hallo Experts,

    Sorry I cannot upload the excel file, b ut will try to explain the situation.

    I had a circular reference problem to solve that I used iteration and the problem was solved but on giving a big direct input no.(for eg. 60000) the solution diverges and give the result #Value. If I slowly increase the value (for eg. 10000, 20000 so on) then it gives the result.

    My question is How can I automatically increase the value to a desired value in the input cell per iteration. So that I don't need to do that manually and It will increase upon iteration.


    Thanks in advance!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Excel diverges during iteration

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

    Please read the yellow banner at the top of the screen. Act on its guidelines and post a SMALL sample sheet. It does NOT have to be your real sheet. Mock up a sample.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    12-06-2019
    Location
    berlin
    MS-Off Ver
    MS office 7
    Posts
    3

    Re: Excel diverges during iteration

    Hi,

    I tried to mock up the sheet. In the sheet, Input = 80000 and I want to change cell C4 1000/iteration and reach 80000 on 80 iteration.


    Thanks in advance!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,346

    Re: Excel diverges during iteration

    Your description feels incomplete, as I don't understand exactly what solution you are trying to get. Some unrelated, unorganized thoughts:

    A look at the algebra of the problem suggests to me that the row of calculations can be rewritten to eliminate the circular reference. It doesn't change the result, suggesting that the circular reference is neither contributing to or hindering the solution. I can change G11 to =$C$6+SUM(F12:F77) and the results are exactly the same but the circular reference is now gone. Not know exactly how this snippet fits into the larger spreadsheet, I can't say for sure exactly how to eliminate the circular reference in your real sheet (or if it is even possible). It might be worth some time/effort to see if you can eliminate the circular reference.

    I notice that you do not include any error trapping in your circular reference loop. I find that I almost always want some kind of "reset on error" trap in the loop to catch any errors and prevent them from killing the loop. Something like =IFERROR(circular reference formula,reset value) somewhere in the loop. Again, since I don't understand exactly what your calculation is doing, it is difficult to recommend a specific formula.

    The idea of starting C4 at some value (60000) and adding 1000 until C4 is 80000 sounds more like a data table operation to me (https://www.excel-easy.com/examples/data-tables.html if you are unfamiliar with data tables). To illustrate, I put 10000, 20000, 30000, etc. into N2:N9. Enter =D11 into O1 and copy into P1:S1. Then select N1:S9, call up the Data Table command, and put C4 into the column input cell field. Upon pressing OK, the table should fill in with the appropriate values -- with or without the circular reference.

    It's not much, but maybe one or more of those ideas will help point you to a solution you can work with, or allow you to explain your exact problem better.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    12-06-2019
    Location
    berlin
    MS-Off Ver
    MS office 7
    Posts
    3

    Re: Excel diverges during iteration

    Hello MrShorty,

    I tried some Mathematically equation with the IFERROR idea and it had worked.

    you are a Rockstar


    Thanks alot

+ 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. Run an entire loop instead of iteration by iteration when stepping into code?
    By Norcal1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2019, 04:20 PM
  2. Excel Iteration problem
    By Jayant shettigar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2016, 06:13 PM
  3. Excel iteration ranges?
    By Aemornion in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2012, 04:12 AM
  4. Iteration in excel
    By DamirDz in forum Excel General
    Replies: 3
    Last Post: 12-09-2011, 12:07 AM
  5. Using the iteration function in Excel
    By wetandrusting in forum Excel General
    Replies: 2
    Last Post: 03-28-2009, 09:50 PM
  6. Iteration using Excel?
    By chmitchell in forum Excel General
    Replies: 1
    Last Post: 02-16-2009, 06:05 PM
  7. Iteration in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-15-2007, 03:02 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