# how to overcome circular references

#### ridgetown_rick

##### Board Regular
I have a perplexing problem. The scenario is: I have a column containing daily soil moisture levels (calculated from inputs and losses) from which I can determine the amount of irrigation that is needed (if any). The trouble is, if irrigation is needed, this must now be added to the soil moisture level before the next calculation can be made. The formula then results in a circular reference. My VB skills are limited and a bit rusty, my excel + VBA skills even more so. Any suggestions on how to overcome this circular ref. problem? I can supply snippets of the spreadsheet if necessary.

rg

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

##### MrExcel MVP
You could post the formulas in question along with a description of the logic used...

#### ridgetown_rick

##### Board Regular
Below are the formulas for my spreadsheet described in the form of cell -> column title -> contents (I tried downloading the addin to post the spreadsheet, but it wouldn't install on my machine).

My problem is, in order to calculate col. I, I need to add the irrigation value from cell H to that in cell G. However, when irrigation is added, I now need to make this (soil water + irrigation) value the new value in G, from which the value of I is derived. Thus the circular reference problem.

In other words, I need to add the irrigation into the soil moisture before I go to the next day's calculations to determine what H should be.

Each worksheet will represent a complete growing season and only some periods will require irrigation (when soil moisture drops below a critical level). Irrigating then brings the soil moisture up to a new level. Is this too confusing?

B13 -> Crop Factor -> =IF(O13>1700,1,IF(O13>1000,0.7,IF(O13>120,0.4,0.2)))
C13 -> Evaporation -> hardcoded values ranging from .2 to .7
D13 -> Wilt Factor -> =IF(G13/\$B\$4>0.8,1,IF(G13/\$B\$4>0.7,\$B\$8,IF(G13/\$B\$4>0.6, \$B\$7,IF(G13/\$B\$4>0.5,\$B\$6, 0))))
E13 -> Water Loss -> =B13*C13*D12
F13 -> rainfall -> hardcoded values ranging from 0 to 40
G13 -> Soil Water Balance -> =IF(G12-E13+F13>\$B\$4,\$B\$4,(G12-E13+F13))
H13 -> Minimum Irrigation -> =IF(J13<0.7,\$B\$4*0.85-G12,0)
I13 -> Irrigated Water Balance -> =G64+H64
J13 -> % of Capacity -> =G64/160
K13 -> Min Temperature -> hardcoded values (-1 to 25)
L13 -> Max Temperature -> hardcoded values (14 to 35)
O13GDD -> =(((((9/5)*K32)+32)+((9/5)*L32)+32)/2-50)+O31

Replies
6
Views
720
Replies
0
Views
274
Replies
0
Views
343
Replies
1
Views
346
Replies
1
Views
87

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,109
Messages
5,768,148
Members
425,458
Latest member
Jaspal1996

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back