SciELO - Scientific Electronic Library Online

vol.61 issue6Nitrate reductase activity in leaves and stems of tanner grass (Brachiaria radicans Napper)Cooling parameters for fruits and vegetables of different sizes in a hydrocooling system author indexsubject indexarticles search
Home Pagealphabetic serial listing  

Services on Demand




Related links


Scientia Agricola

On-line version ISSN 1678-992X

Sci. agric. (Piracicaba, Braz.) vol.61 no.6 Piracicaba Nov./Dec. 2004 



A simplified Excel® algorithm for estimating the least limiting water range of soils


Um algoritimo simplificado, desenvolvido em Excel®, para estimativa do intervalo hídrico ótimo dos solos



Tairone Paiva LeãoI; Alvaro Pires da SilvaII, *

IUniversity of Tennessee - Dept. of Earth and Planetary Sciences, Graduate Program in Hydrogeology, 1412 Circle Drive 306 - Earth Planetary Sciences Bldg, 37996-1410 - Knoxville, Tennessee, USA
IIUSP/ESALQ - Depto. de Solos e Nutrição de Plantas, Av. Pádua Dias, 11, C.P. 9 - 13418-900, Piracicaba, SP - Brasil




The least limiting water range (LLWR) of soils has been employed as a methodological approach for evaluation of soil physical quality in different agricultural systems, including forestry, grasslands and major crops. However, the absence of a simplified methodology for the quantification of LLWR has hampered the popularization of its use among researchers and soil managers. Taking this into account this work has the objective of proposing and describing a simplified algorithm developed in Excel® software for quantification of the LLWR, including the calculation of the critical bulk density, at which the LLWR becomes zero. Despite the simplicity of the procedures and numerical techniques of optimization used, the nonlinear regression produced reliable results when compared to those found in the literature.

Key words: nonlinear regression, spreadsheet software, optimization, soil physics, soil quality


O intervalo hídrico ótimo (IHO) dos solos tem sido empregado como uma metodologia para a avaliação da qualidade física do solo em diferentes sistemas agrícolas, incluindo áreas florestais, pastagens e grandes culturas. Entretanto, a inexistência de uma metodologia simplificada para a quantificação do IHO tem dificultado a popularização do uso desta técnica entre pesquisadores e técnicos. Levando isto em consideração, este trabalho tem como objetivo propor e descrever um algoritmo simplificado, desenvolvido em planilha eletrônica Excel®, para quantificação do IHO, incluindo o cálculo da densidade do solo crítica, na qual o IHO é nulo. Apesar da simplicidade dos procedimentos e técnicas numéricas de otimização utilizados, a regressão não-linear produziu resultados confiáveis quando comparados com aqueles encontrados em literatura.

Palavras-chave: regressão não-linear, planilha eletrônica, otimização, física do solo, qualidade do solo




The concept of an index of optimum soil water content for plant growth, as related to soil physical properties was introduced by Letey (1985) and identified as "non-limiting water range" (NLWR). Later, Silva et al. (1994) developed the NLWR concept quantitatively, renaming it as the least limiting water range (LLWR). For a given soil type, the LLWR incorporates the limitations of soil aeration, matric suction and soil penetration resistance for root growth as a function of a single variable (i.e. soil bulk density).

Since its quantification (Silva et al., 1994), the LLWR has been employed as an approach for assessing soil physical quality in a wide range of management systems and soils (Tormena et al., 1999; Sharma & Bhusan, 2001; Wu et al., 2003), including in its relation to soil chemical properties (Drury et al., 2003). The LLWR has also been cited as a methodological approach for soil quality assessment in literature reviews (Lal, 2000; Schoenholtz et al., 2000) and books (Brady & Weil, 1999; Silva et al., 2002). Despite advances in the characterization and quantification of the LLWR, a detailed description of the computational methodology for calculating the LLWR from soil properties data, including data management, curve fitting procedures, and graphing techniques is still lacking.

The objective of this work was to propose a simplified algorithm for estimation of the least limiting water range of soils using the spreadsheet software Microsoft Excel®1 and discuss technical issues concerning the nonlinear curve fitting procedures. We chose to use Excel® spreadsheet because it is one of the most popular commercial spreadsheets in use in Brazil and many other countries. However, the methodology described here can be adapted to other software packages, according to the resources and knowledge of the user.



The quantification of the Least Limiting Water Range (LLWR) is based on the fitting of a soil water retention function and a soil penetration resistance function. The soil water retention function in this specific case must take into account the soil structural variability, which may be achieved by incorporating the soil bulk density in the equation. Silva et al. (1994) incorporated the soil bulk density variability in a simple power function employed by Ross et al. (1991) for fitting the water retention data:

q = Soil volumetric water content [L3 L-3]; Y = Matric suction [M L-1 T-2]; a, b = Empirical parameters.

The stepwise regression procedures of Silva et al. (1994) resulted in a three-parameter nonlinear equation with good fitting properties for characterizing the soil structural influence on the soil water retention phenomena (Tormena et al., 1998; Betz et al., 1998):

q = Soil volumetric water content [L3 L-3]; Db = Soil bulk density [M L-3]; Y = Matric suction [M L-1 T-2]; a, b, and c = Empirical parameters.

The soil penetration resistance function has been adequately described using the nonlinear equation proposed by Busscher & Sojka (1987) (Silva et al., 1994; Betz et al., 1998; Leão, 2002).

SR = Soil penetration resistance [M L-1 T-2]; q = Soil volumetric water content [L3 L-3]; Db = Soil bulk density [M L-3]; d, e, and f = Empirical parameters.

The equations 2 and 3 are transformable to linear form, via logarithmic transformation. Because of the relative simplicity of using linear regression methods, some researchers do prefer to work with them in the linearized form (Silva et al., 1994; Betz et al., 1998; Zou et al., 2000). However, we chose not to do so because i) Given the availability of efficient nonlinear algorithms, the usefulness of linearization is somewhat diminished (Seber & Wild, 1989); and ii) The transformation of the data usually involves a transformation of the error term too, which affects the underlying assumptions (Bates & Watts, 1988).



The sampling and data collection methodology for the LLWR quantification has been exhaustively described in other publications (Silva et al., 1994; Leão, 2002; Silva et al., 2002). Briefly, it is necessary to collect a set of undisturbed soil cores in the experimental site to be evaluated. The cores are then taken to the laboratory and saturated with water. In the lab, each core is equilibrated at different and increased matric suctions (Klute, 1986). The soil penetration resistance, water content and bulk density are then determined in each core. The result is a set of soil penetration resistance (SR), volumetric water content (q), bulk density (Db), and matric suction (Y) data points for each one of the cores. The data is fitted to the Equations 2 and 3, resulting in a set of empirical parameters a, b, c, d, e and f that are used in the quantification of the LLWR.

In the Excel® worksheet example described here, the first four columns are the data from the silt loam soil described by Silva et al. (1994) (Figure 1). The soil is an Aquic Eutrochrept with 180 g kg-1 clay, 520 g kg-1 silt, 300 g kg-1 sand and 38 g kg-1 organic matter, and mean bulk density of 1.47 g cm-3. For the optimization procedures, it is necessary to create two other columns, with the estimated values for the volumetric water content and soil penetration resistance. This can be achieved by creating columns with Equations 2 and 3, using guess estimates for the empirical parameters. It is advisable that the estimates should be taken from the literature, since reasonable initial parameter estimates are critical for convergence of the optimization algorithms in nonlinear regression (Wraith & Or, 1998). Besides the estimates for the q and SR equations it is also necessary to create columns with the squared error (deviate) for these variables (Figure 2). This is necessary for the quantification of the sum of squared error estimates. The squared error is calculated by:





With the squared errors for q and SR, it is possibly to establish cells containing the sum of squared errors (SSE) for fitting the Equations 2 and 3. The SSE is the merit function to be minimized in the nonlinear problem optimization in this case. The minimization procedure in Excel® is executed by the command "solver". Once the "target cell" is defined, which in this case is the cell containing the sum of squared errors; the option "min" must be selected (Figure 3). The "changing cells" are the parameter estimates previously used to calculate the squared error terms for the variables (Figure 2). It is worth noting that the optimization procedure can be easily subject to constraints. In general, it is not necessary to apply any constraints to fit the equations used in the calculations of the LLWR, as long as good initial parameter estimates are provided. However, for data sets in which the user suspects low correlation coefficients between the variables it is advisable to apply constraints. The range values for the constraints must be assumed according to the literature and practicality.



The minimization procedure must be executed independently for variables and parameters of Equations 2 and 3. The goodness of fit can be easily computed from the variance of measured values (a spreadsheet built-in statistical function) by the coefficient of determination (r2) of the resulting curve (Figure 4):



N = Number of data points; s2variable = Variance of the measurements of the independent variable; SSE = Sum of squared errors.

With the parameter estimates, the LLWR for each Db value can be estimated from simple algebraic transformations of equations 2 and 3. It is also necessary to set the critical limits for the physical variables used in the analysis. Here we used the critical values commonly found in the literature. The field capacity matric suction was set at the value of 0.01 MPa (Haise et al., 1955), and the wilting point was set at 1.5 MPa (Richards & Weaver, 1944). The soil penetration resistance value assumed to be limiting for plant growth was set at 2.0 MPa (Taylor et al., 1966) and the limiting air filled porosity was set at 10% (Grable & Siemer, 1968). However, the user is encouraged to change these critical values according to his experimental conditions and knowledge of the physical processes involved in these critical limits.

The variation in water content at field capacity (qfc) and wilting point (qwp) with Db can be found by applying the limiting matric suctions described earlier to Equation 2.

The variation with Db of the water content at which the soil penetration resistance is equal to 2.0 MPa (qsr) can be calculated by isolating the water content in Equation 3.

The variation with Db of the water content at which the air filled porosity equals 10% (qafp) can be found from the bulk density and particle density (Dp), here assumed as 2.65 g cm-3.

The parameters and variables in Equations 6, 7, 8 and 9 are the same as described earlier. The upper limit (UL) of the LLWR can be determined by the lower value of either qfc or qafp. The lower limit (LL) of the LLWR can be found by the higher value of either qsr or qwp. The LLWR is calculated as LLWR = UL – LL and as negative values have no physical meaning in this case, it is necessary to set negative values to zero. This can be easily achieved in Excel® using the built-in function "IF". Figure 5 illustrates the columns with the predicted values of qfc, qwp, qafp and qsr, along with the results for the UL, LL and LLWR values calculated using "IF" blocks.



The calculation of soil critical bulk density value (Dbc) from LLWR is the last and sometimes the most important step (from soil management point of view) in the evaluation of the LLWR. To calculate the Dbc it is necessary to know the equations for the variables qfc, qwp, qafp, and qsr (Equations 6, 7, 8 and 9) and then determine when UL and LL converge so that the LLWR equals zero. This can be achieved by analyzing which variables in data set defines the first point in which the LLWR equals zero, or by a previous graphic analysis, plotting the variables qfc, qwp, qafp, and qsr as a function of Db. Once the equations of the variables that intercept at the point where the LLWR equals zero are identified, two more cells with these equations need to be established. However, the equations are drawn as a function of a dummy Db value that will be optimized to find the Dbc value. Another cell with a dummy value of the ULequation - LLequation also need to be created (Figure 6).



At this point, the "solver" command is used again. The "target cell" will be the dummy value of ULequation - LLequation. The option "min" is checked and the "changing cell" will be the cell containing the dummy Db value (Figure 6). It is necessary to add a constraint pertaining to the dummy value cell (ULequation - LLequation) that can not be less than zero in the optimization. After the optimization procedure, the Dbc will be equal to the dummy Db, as shown in Figure 6.

The characteristic LLWR graphics resulting from the procedures described above are illustrated in Figures 7 and 8. Despite the use of simplified statistical procedures, similar results were found in comparison to those of Silva et al. (1994). The critical bulk density found by Excel® Solver (Dbc) was 1.55 g cm-3, while Silva et al. (1994) found 1.56 g cm-3. This slight difference in the values could be attributed to the statistical approaches employed in each case. Silva et al. (1994) used linear regression while we choose to use nonlinear regression, for the simplicity of use in this specific case, and to avoid the necessity of linearization of the models, as discussed earlier. In the Excel® worksheet presented here, Figures 7 and 8 are automatically plotted using the adjusted coefficients for the models, avoiding the necessity of plotting procedures after the statistical analysis. Figure 7 presents the LLWR critical limits by aeration (qafp), field capacity (qfc), wilting point (qwp) and soil penetration resistance (qsr) plotted as a function of soil bulk density (Db). An estimate of the dummy Db value and the equations that intercept when the LLWR becomes zero are easily visualized in Figure 7. This information can be used to facilitate the critical bulk density estimation procedure described earlier.





Although not estimated in the worksheet presented here, other relevant Db values for the LLWR evaluation can be easily quantified using the procedures described above. These values are: (i) the Db at which qafp replaces the qfc as the LLWR upper limit, and (ii) the Db at which qsr replaces qwp as the LLWR lower limit (Silva & Kay, 1997). Other relevant combinations can be found according to the characteristics of the user's data.

Another graph that has been widely used in the characterization and interpretation of the LLWR is illustrated on Figure 8. The variation of the LLWR is presented as a function of soil Db. From similar graphs, Tormena et al. (1999) were able to identify important trends in LLWR data, like ranges of Db values for which the LLWR is positively correlated, and the Db value at which LLWR starts to decrease steeply.



The simplified algorithm presented in this work is an alternative for the time consuming statistical analysis and plotting procedures used in the quantification and evaluation of the least limiting water range as an index of soil physical quality. Despite the simplicity of the procedures and numerical techniques of optimization used here, the nonlinear regression produced reliable results when compared to those found in the literature. The critical bulk density value and graphs of LLWR data are also produced by the algorithm, enhancing the interpretation of the results. The Excel® worksheet is available through contact with the first author:



BATES, D.M.; WATTS, D.G. Nonlinear regression analysis and its applications. New York: John Wiley & Sons, 1988. 365p.         [ Links ]

BETZ, C.L.; ALLMARAS, R.R.; COPELAND, S.M.; RANDALL, G.W. Least limiting water range: traffic and long-term tillage influences in a Webster soil. Soil Science Society of America Journal, v.62, p.1384-1393, 1998.         [ Links ]

BRADY, N.C.; WEIL, R.R. The nature and properties of soils. 12.ed. New Jersey: Prentice Hall, 1999. 881p.         [ Links ]

BUSSCHER, W.J.; SOJKA, R.E. Enhancement of subsoiling effect on soil strength by conservation tillage. Transactions of the ASAE, v.30, p.888-892, 1987.         [ Links ]

DRURY, C.F.; ZHANG, T.Q.; KAY, B.D. The non-limiting and least limiting water ranges for soil nitrogen mineralization. Soil Science Society of America Journal, v.27, p.1388-1404, 2003.         [ Links ]

GRABLE, A.R.; SIEMER, E.G. Effects of bulk density, aggregate size, and soil water suction on oxygen diffusion, redox potential and elongation of corn roots. Soil Science Society of America Journal, v.32, p.180-186, 1968.         [ Links ]

HAISE, H.R.; HAAS, H.J.; JENSEN, L.R. Soil moisture studies of some Great Plains soils. II. Field capacity as related to 1/3-atmosphere percentage, and minimum point as related to 15- and 26-atmosphere percentage. Soil Science Society of America Proceedings, v.19, p.20-25, 1955.         [ Links ]

KLUTE, A. Methods of soil analysis: physical and mineralogical methods. 2.ed. Madison: ASA, 1986. cap.26, p.635-660: Water retention: laboratory methods.         [ Links ]

LAL, R. Physical management of soils of the tropics: priorities for the 21st century. Soil Science, v.135, p.191-207, 2000.         [ Links ]

LEÃO, T.P. Intervalo hídrico ótimo em diferentes sistemas de pastejo e manejo da pastagem. Piracicaba: USP/ESALQ, 2002. 58p. (Dissertação - Mestrado).         [ Links ]

LETEY, J. Relationship between soil physical properties and crop production. Advances in Soil Science, v.1, p.277-294, 1985.         [ Links ]

RICHARDS, L.A.; WEAVER, L.R. Fifteen atmosphere percentage as related to the permanent wilting point. Soil Science, v.56, p.331-339, 1944.         [ Links ]

ROSS, P.J.; WILLIAMS, J.; BRISTOW, K.L. Equations for extending water-retention curves to dryness. Soil Science Society of America Journal, v.55, p.923-927, 1991.         [ Links ]

SCHOENHOLTZ, S.H.; VAN MIEGROET, H.; BURGER, J.A. A review of chemical and physical properties as indicators of forest soil quality: challenges and opportunities. Forest Ecology and Management, v.138, p.335-356, 2000.        [ Links ]

SEBER, G.A.F.; WILD, C.J. Nonlinear regression. New York: John Wiley & Sons, 1989. 768p.         [ Links ]

SHARMA, P.K.; BHUSHAN, L. Physical characterization of a soil amended with organic residues in a rice-wheat cropping system using a single value soil physical index. Soil and Tillage Research, v.60, p.143-152, 2001.         [ Links ]

SILVA, A.P.; KAY, B.D. Estimating the least limiting water range of soils from properties and management. Soil Science Society of America Journal, v.61, p.877-883, 1997.         [ Links ]

SILVA, A.P.; KAY, B.D.; PERFECT, E. Characterization of the least limiting water range of soils. Soil Science Society of America Journal, v.58, p.1775-1781, 1994.         [ Links ]

SILVA, A.P.; IMHOFF, S.C.; TORMENA, C.A.; LEÃO, T.P. Avaliação da compactação de solos florestais. In: GONÇALVES, J.L.M.; STAPE, J.L. Conservação e cultivo de solos para plantações florestais. Piracicaba: FEALQ, 2002. cap.10, p.351-372.         [ Links ]

TAYLOR, H.M.; ROBERSON, G.M.; PARKER, J.J. Soil strength-root penetration relations to coarse textured materials. Soil Science, v.102, p.18-22, 1966.         [ Links ]

TORMENA, C.A.; SILVA, A.P.; LIBARDI, P.L. Caracterização do intervalo hídrico ótimo de um Latossolo Roxo sob plantio direto. Revista Brasileira de Ciência do Solo, v.22, p.573-581, 1998.         [ Links ]

TORMENA, C.A.; SILVA, A.P.; LIBARDI, P.L. Soil physical quality of a Brazilian Oxisol under two tillage systems using the least limiting water range approach. Soil and Tillage Research, v.52, p.223-232, 1999.         [ Links ]

WRAITH, J.M.; OR, D. Nonlinear parameter estimation using spreadsheet software. Journal of Natural Resources and Life Sciences Education, v.27, p.13-19, 1998.         [ Links ]

WU, L.; FENG, G.; LETEY, J.; FERGUSON, L.; MITCHELL, J.; McCULLOUGH-SANDEN, B.; MARKEGARD, G. Soil management effects on the nonlimiting water range. Geoderma, v.114, p.401-414, 2003.         [ Links ]

ZOU, C.; SANDS, R.; BUCHAN, G.; HUDSON, I. Least limiting water range: a potential indicator of physical quality of forest soils. Australian Journal of Soil Research, v.38, p.947-958, 2000.         [ Links ](Footnotes)



Received December 05, 2003
Accepted September 13, 2004



* Corresponding author <>
1 Mention of company names or products does not imply in endorsements by the authors or their institution.

Creative Commons License All the contents of this journal, except where otherwise noted, is licensed under a Creative Commons Attribution License