+ Reply to Thread
Results 1 to 4 of 4

Min function with circularity

  1. #1
    Registered User
    Join Date
    05-26-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    11

    Min function with circularity

    Hello all,

    Its been a while since I posted on here, but hoping y'all could help an Excel dummy again.

    I've attached a very simple workbook that should help explain the issue I'm having.

    I am looking to take the minimum number (B12) of a range of numbers (B9:H9). I then use the minimum number (B12) to modify a number above the range of numbers.

    I would think that with circularity on Excel would calculate the correct minimum range and increase it appropriately, but it doesn't seem to be happening.

    Sorry if this is unclear, I can answer any questions anyone might have.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,811

    Re: Min function with circularity

    I see that Excel's circular calculation engine is having some problems with this. Part of the problem is that, as near as I can tell, the problem really doesn't converge. If I have reverse engineered the problem, it essentially boils down to:
    10-(5+x(i)-1-1-1)=x(i+1)
    So, when x(i) is 3, x(i+1) is 5. Put that in for x(i) and x(i+1) is 3, and the iteration merely oscillates between 3 and 5. Because of the way it's set up, the actual oscillation in the spreadsheet is more complex, but that is basically what it is doing -- oscillating between 3 and 5.
    As this is a simplified calculation, and may not adequately represent your real calculation, what are you really trying to do? As near as I can tell, the exact problem in your example has no good solution because of the numerical instability. I am guessing that there should be a better algorithm that will more reliably converge on your desired solution.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-26-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Min function with circularity

    MrShorty

    Thanks for the quick answer.

    What I am trying to do is have excel automatically add a one-time increase in cell B4 so that the numbers in row 9 never falls below 10. In the attached example the answer is 9, but in the complex version of this all the numbers will be changing and I'd like this to be automatically calculated.

    Happy to consider other possible alternatives to this.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,811

    Re: Min function with circularity

    That is simple algebra (at least, in your sample sheet, I still cannot speak for your real sheet).

    Row 9: 5+(10-11+x)+(10-11)+(10-11)+(10-11)>=10 solve for x and x>=9 (I will assume that your algebra skills are up to that simple challenge. You may review something like this, if you need an algebra refresher: https://www.purplemath.com/modules/ineqlin.htm ).

    The 5 comes from B7, the (10-11+x) is the sum of B2:B4, and the (10-11)s are the sums of C2:E4. I am ignoring F2:H4, because those are all 0.

    I expect the hardest part of this in your real sheet is going to be identifying the month with the minimum in it, which should not be overly difficult. Once you identify the month, then the solution should be simple algebra like this. It might be useful to have a couple more examples that show minima in the middle months rather than at the end month (unless it always will be the last month like your example shows).

+ 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. COUNTUNIQUE Function in Google Sheets; Excel lacks a direct counterpart to this function?
    By PivotTablePSHomage in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2018, 05:27 AM
  2. Calling function inside function. (aka nested function)
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2015, 05:58 AM
  3. Replies: 0
    Last Post: 11-14-2014, 07:10 AM
  4. Replies: 13
    Last Post: 04-08-2014, 05:46 AM
  5. macro to break circularity
    By neodjandre in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2007, 10:33 PM
  6. [SOLVED] Circularity
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-24-2006, 07:10 AM
  7. circularity
    By Dean in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-03-2005, 11:06 PM

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