+ Reply to Thread
Results 1 to 4 of 4

Increase in Value, Circular Reference Issue

  1. #1
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Increase in Value, Circular Reference Issue

    The problem I am having feels like a simple one that I can only see a very complicated solution to. So hopefully someone else can come up with an easy way to resolve this.

    I have 3 cells, an initial amount (A2), the new amount (B2) and the increase value (C2).

    Cell A2 always stays the same, 100.
    Cell B2 needs to increase by the amount in C2.
    Cell C2 is a random number between 1 and 20.

    What currently happens is when I refresh the random number, I get this happening:
    Refresh 1: 100 105 5
    Refresh 2: 100 111 11
    Refresh 3: 100 102 2
    Refresh 4: 100 113 13

    What I want to happen is this:
    Refresh 1: 100 105 5
    Refresh 2: 100 116 11
    Refresh 3: 100 118 2
    Refresh 4: 100 131 13

    I want the Value in B2 to increase with each refresh, rather than replace. Anyone have any ideas?

    ((Currently having issues uploading a practice sheet and will upload one asap, for now the 3 formulas from the cells are simply: (A2 100 (B2 =A2+C2 (C2 =RANDBETWEEN(1,20).))

  2. #2
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Increase in Value, Circular Reference Issue

    Do you need it in only 1 row? If so, you'll need VBA to solve this.

    Otherwise, perhaps consider this layout instead.
    Row\Col
    A
    B
    C
    D
    1
    Initial number New amount Increase value
    2
    Refresh 1 100 106 6
    3
    Refresh 2 109 3
    4
    Refresh 3 128 19
    5
    Refresh 4 133 5
    6

    where...
    C2: = B2 + D2
    C3: = C2 + D3
    D2:D5 = RANDBETWEEN(1,20)

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Increase in Value, Circular Reference Issue

    [.... deleted ....]
    Last edited by joeu2004; 10-27-2015 at 07:03 PM. Reason: submitted prematurely

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Increase in Value, Circular Reference Issue

    Quote Originally Posted by Mousiefuzz View Post
    Cell A2 always stays the same, 100.
    Cell B2 needs to increase by the amount in C2.
    Cell C2 is a random number between 1 and 20.
    [....]
    I want the Value in B2 to increase with each refresh, rather than replace. Anyone have any ideas?
    Click on File, Options, Formulas, checkmark Enable Iterative Calculation, and enter 1 into Maximum Iterations. Then enter the following formula into B2:

    =IF(B2=0,A2,B2)+C2

    where C2 contains =RANDBETWEEN(1,20).

    Each time you press F9 (or edit any cell!), B2 will be increased by a new value in C2.

    To restart, select B2, press F2, then Enter.

    Personally, I abhor designs that rely on circular references. And I usually do not want RAND and RANDBETWEEN formulas directly in Excel because they change whenever Excel calculates anything (else).

    IMHO, it is better to design a macro to perform the update. You can create a "button" to execute the macro whenever you want, as opposed to when Excel does. Or simple press alt+F8, select the macro name, and click on Run.

    For example:
    Please Login or Register  to view this content.
    Last edited by joeu2004; 10-27-2015 at 07:09 PM. Reason: b2 type Long -> Double

+ 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 Issue to Solve with Macro
    By MVictorH in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2015, 10:19 PM
  2. Circular Reference Issue when Balancing Costs
    By yoman987 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-15-2014, 03:16 PM
  3. [SOLVED] circular reference issue
    By Netaji in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-02-2014, 09:47 AM
  4. Circular reference causing formula issue
    By spacemonkey82 in forum Excel General
    Replies: 3
    Last Post: 09-04-2011, 04:49 AM
  5. Excel 2007 : Circular Reference Issue
    By henro8 in forum Excel General
    Replies: 1
    Last Post: 07-25-2011, 05:54 PM
  6. Circular reference issue. Looking for workaround.
    By krutec in forum Excel General
    Replies: 2
    Last Post: 12-11-2009, 03:20 PM

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