# Increase in Value, Circular Reference Issue

1. ## 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. ## 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. ## Re: Increase in Value, Circular Reference Issue

[.... deleted ....]

4. ## Re: Increase in Value, Circular Reference Issue

Originally Posted by Mousiefuzz
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.``

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

#### 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