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
