# 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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You could post the formulas in question along with a description of the logic used...

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
2
Views
366
Replies
3
Views
689
Replies
7
Views
750
Replies
6
Views
2K
Replies
0
Views
2K

1,218,792
Messages
6,144,513
Members
450,550
Latest member
Melanie_Annies

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