how to overcome circular references

ridgetown_rick

Board Regular
Joined
Aug 28, 2002
Messages
193
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

ridgetown_rick

Board Regular
Joined
Aug 28, 2002
Messages
193
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
 

Forum statistics

Threads
1,148,053
Messages
5,744,531
Members
423,881
Latest member
Nguyen Vu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top