EXCEL ADD-IN TO MODEL THE SOIL COMPRESSION CURVE

ABSTRACT

Soil compression curve (CC) provides parameters to identify soil load-bearing capacity and susceptibility to compaction. An Excel add-in (ACC) incorporating graphical procedures for mathematical models for soil CC description and calculation of parameters was developed. By using the ACC, soil CC can be described by means of the Casagrande method, mathematically operationalized with the van Genuchten equation, with or without restrictions on its parameters, and by Dias Junior and Pierce method in its original form and also modified using the void ratio rather than soil bulk density. The ACC uses a single Excel spreadsheet for input and output data, in addition to a graphical interface and a tool for exporting editable charts. Compared to SAS statistical software, the ACC minimized the sum of squared residuals and estimated parameters of mathematical models with the same efficiency for 347 compression curves. The ACC programming script is available and can be modified or used as a framework for other programming projects.

KEYWORDS:
compressibility; precompression stress; load-bearing capacity

INTRODUCTION

Soil compression curve (CC) has been used to study soil load-bearing capacity (KELLER et al., 2011; SEVERIANO et al., 2010; MACHADO & TREIN, 2013). Originally, soil CC was described as the graphical relation between the base 10 logarithm of the applied load (log₁₀ σ) and the void ratio (ε) (CASAGRANDE, 1936). Subsequently, mathematical relationships between log₁₀ σ and ε and between log₁₀ σ and soil bulk density (ρ) were proposed to describe soil CC. Regardless the strategy, the main objective is to determine the precompression stress (PCS), which may indicates soil load-bearing capacity, and the compressibility coefficient or compression index (CI), which indicates soil susceptibility to compaction.

The inherent subjectivity of graphical methods has raised several mathematical proposals in recent years. DIAS JUNIOR & PIERCE (1995) proposed a simple mathematical technique (combination of straight lines) to determine soil PCS and CI values. Unlike the straight line technique, continuous functions across σ range have also been used to model CC. Examples for functions ε = f (log₁₀ σ) are the models described in REINERT et al. (2003) and BAUMGARTL & KÖCKB (2004); for functions ρ = f (log₁₀ σ), one of the examples is the model described in FRITTON (2001).

The set of papers published on this topic indicates most research groups use the function models ε = f (log₁₀ σ). In addition, the equation used by VAN GENUCHTEN (1980) to describe the water retention curve, adapted for compressibility data, is the most commonly used (BAUMGARTL & KÖCKB, 2004; REINERT et al., 2003; VOGELMAN et al., 2012; MENTGES et al., 2013).

REINERT et al. (2003) used a restricted form of VAN GENUCHTEN (1980) equation for developing a PCS/CI calculation software. The authors assumed as a boundary condition ε_f = 0 (final void ratio) and conditioned the parameter m as a function of the parameter n (m = 1 – 1/n), which modifies the original equation (BAUMGARTL & KÖCKB, 2004) for the simplified form $\text{ε}={\text{ε}}_{\mathrm{i}}{\left[1+{\left(\text{ασ}\right)}^{\mathrm{n}}\right]}^{-\left(1-\frac{1}{\mathrm{n}}\right)}$. Furthermore, in the adjustment process, ε_i (initial void ratio) is maintained constant since, physically, it represents ε for a particular condition without load application (σ = 0: sample without deformation). Although the above equation is physically coherent since it results in ε = ε_i when σ = 0, in practice the calculated value of σ_i may be an false value since ensuring the perfect fit for the load application device on the sample surface without receiving load and suffering deformation is a difficult task. Consequently, the equation restriction may introduce deviations in the curve fitted to the data and change PCS estimation.

The aim of this study was to develop an Excel add-in that allows calculating PCS and CI, expanding CC modeling options by using the Casagrande method, mathematically operationalized with the van Genuchten equation, with and without restrictions on its parameters, and by the Dias Junior and Pierce method in its original form and modified for void ratio.

MATERIAL AND METHODS

The add-in was developed in Visual Basic for Applications (VBA), which is a Microsoft Visual Basic implementation built in Microsoft Office programs. In the add-in, three options of PCS and CI calculation were included, all determined mathematically to avoid user subjectivity.

First option – Casagrande

The CASAGRANDE (1936) method was used by mathematically operationalizing with the VAN GENUCHTEN (1980) equation, as proposed by BAUMGARTL & KÖCKB (2004). The first step is to determine the point of maximum curvature (PMC) of the function ε = f (log σ), where log σ represents the base 10 logarithm of σ. The curvature (C) of ε = f (log σ) at any σ value can be calculated by the expression $C=|\epsilon ″|/{[1+\epsilon {\prime }^2]}^{\frac{3}{2}}$, where ε′ and ε″ are the first and second derivatives of the function ε = f (log σ), respectively. In CC ε = f (log σ), PCS corresponds to the minimum of the second derivative and to the root of the third derivative (Figure 1). In the same CC ε = f (log σ), the inflection point (IP) corresponds to the minimum of the first derivative and to the root of the second derivative.

FIGURE 1
Characteristic points of the functions ε = f (log₁₀ σ), ε′ = f (log₁₀ σ), ε″ = f (log₁₀ σ), ε″′ = f (log₁₀ σ), and curvature (C), allowing mathematically calculating the point of maximum curvature (PMC) and inflection point (IP).

The function ε = f (log σ) was described by the VAN GENUCHTEN (1980) equation, adapted for compressibility data:

(1) $$\text{ε}={\text{ε}}_{\mathrm{f}}+({\text{ε}}_{\mathrm{i}}-{\text{ε}}_{\mathrm{f}}){\left[1+{(\text{ασ})}^{\mathrm{n}}\right]}^{-\mathrm{m}}$$

where,

ε_i and ε_f are the initial and final void ratios, respectively, and

α, n, and m are the model adjustment coefficients.

In the graphical representation of CC, σ values are represented in logarithmic scale of base 10. Thus, by defining x = log₁₀(σ) ⇔ σ = 10^x, [eq. (1)] becomes

(2) $$\text{ε}={\text{ε}}_{\mathrm{f}}+({\text{ε}}_{\mathrm{i}}-{\text{ε}}_{\mathrm{f}}){\left[1+{(\text{α}{10}^{\mathrm{x}})}^{\mathrm{n}}\right]}^{-\mathrm{m}}$$

The first derivative of [eq. (2)] is given by:

(3) $$\mathrm{e}\prime =-{\text{mnα}}^{\mathrm{n}}\left({\text{ε}}_{\mathrm{i}}-{\text{ε}}_{\mathrm{f}}\right)\ln 10{\left(1+{(\text{ασ})}^{\mathrm{n}}\right)}^{-\mathrm{m}-1}{\text{σ}}^{\mathrm{n}}$$

The second derivative of [eq. (2)] is given by:

(4) $$e^{″}=-m{n}^2{\alpha }^n\left({\epsilon }_i-{\epsilon }_f\right){\left(ln10\right)}^2{\left(\left(1+{\left(\alpha \sigma \right)}^n\right)\right)}^{-m-1}{\sigma }^n+\left(-m-1\right){\sigma }^n{\left(1+{(\alpha \sigma )}^n\right)}^{-m-2}{\sigma }^{2n}$$

The third derivative of [eq. (2)] is given by:

(5) $$e^{‴}=A{B}^{-m-2}\left(\mathrm{B}+\left(-\mathrm{m}-1\right){\left(\text{ασ}\right)}^{\mathrm{n}}+2\left(-\mathrm{m}-1\right){\left(\text{ασ}\right)}^{\mathrm{n}}+\left(-\mathrm{m}-1\right)\left(-\mathrm{m}-2\right){\left(\text{ασ}\right)}^{2\mathrm{n}}{\mathrm{B}}^{-1}\right)$$

where,

A = –mn³αⁿ(ε_i –ε_f)σⁿ(ln 10)³, and

B = 1 + (ασ)ⁿ

The inflection point (IP) was obtained analytically, equaling the second derivative to zero:

(6) $${\epsilon }^{″}=0\to IP=\frac{1}{\alpha }{\left(\frac{1}{m}\right)}^{\frac{1}{n}}$$

The point of maximum curvature (PMC) was obtained numerically with the search algorithm of the root of Equation (5) by the bisection method.

Equation (3) is used to calculate the slope of tangent lines at IP and PMC. The virgin line (VL) is defined as the tangent line at IP. The bisector line is drawn from PMC, dividing in half the angle formed between the tangent line at PMC and the parallel line to the abscissa, also drawn from PMC. PCS is defined as the σ value at VL intersection with the bisector line, and CI is defined as the slope modulus of the tangent line at IP.

When CC assumes a shape other than sigmoidal, IP may be greater than the highest load used in the test (KELLER et al., 2011). For these situations, the add-in offers the option of choosing a set of pairs (σ, ε) from the end of CC to draw VL, whose straight line coefficients are estimated by the following functions available in VBA: WorksheetFunction.Slope() and WorksheetFunction.Intercept().

Equation (1) is adjusted to the experimental data by the Excel Solver tool, which is automatically configured by the add-in programming for each case of equation adjustment. Equation (1) adjustment can be performed without any restriction (by estimating ε_i, ε_f, α, n, and m) or with restrictions (ε_i and ε_f with constant values defined by the user, and m can be conditioned as m = 1 – 1/n). The user can choose one or even all restrictions. In addition, Solver was configured to use quadratic estimation, central derivative, conjugate search, maximum of 1000 iterations, the maximum time of 100 seconds, tolerance of 1%, and convergence and precision equal to 10^‒12.

Second option – Dias Junior & Pierce

The DIAS JUNIOR & PIERCE (1995) method, which uses two regression lines adjusted to compression data, was used. CI is determined by the slope of the adjusted VL over the last two pairs of data (ρ, log₁₀ σ) of the test, and PCS is determined as the σ value at VL intersection with the secondary compression line, which is adjusted using from two to five pairs of data (ρ, log₁₀ σ) from the beginning of the test.

(7) $${\text{ρ}}_{\text{RV}}={\mathrm{a}}_1\log \text{σ}+{\mathrm{b}}_1$$

where,

ρ_RV, a₁, and b₁ are the soil bulk density of VL and the straight-line adjustment parameters.

(8) $${\text{ρ}}_{\text{RC}}={\mathrm{a}}_2\log \text{σ}+{\mathrm{b}}_2$$

where,

ρ_RC, a₂, and b₂ are the soil bulk density of the secondary compression line and the straight-line adjustment parameters.

The number of points of each line depends on sample moisture (DIAS JUNIOR & PIERCE, 1995), which can be defined by the user (between 2 and 6 points). The values of straight-line coefficients are estimated using the following functions available in VBA: WorksheetFunction.Slope() and WorksheetFunction.Intercept(). CI is considered as the modulus of the parameter a₁, which defines VL slope. PCS is considered as the σ value at VL intersection with the secondary compression line.

(9) $$PCS={10}^{\left(\frac{b_1-b_2}{a_2-a_1}\right)}$$

Third option – Intersection of ε straight lines

Here ε replaces ρ in DIAS JUNIOR & PIERCE (1995) method. Calculations are performed in the same way as described for eqs. (7), (8) and (9). ROSA et al. (2011) have already used this option.

To relate σ with ρ and total porosity (φ) by means of a continuous function, the add-in contains options to meet the maximum σ to be applied to the sample to avoid any increase in ρ or decrease in φ in the limits established by the user. For both ρ and φ, a simplified form of [eq. (1)] was used.

(10) $$\rho =k{\left[1+{\left(\alpha \sigma \right)}^n\right]}^{-m}$$

(11) $$\phi =k{\left[1+{\left(\alpha \sigma \right)}^n\right]}^{-m}$$

where,

k, α, n, and m are the model adjustment coefficients.

For instance, the user sets a limit ρ value, and the add-in calculates the maximum σ that can be applied to the sample:

(12) $$\sigma =\frac{1}{\alpha }{\left[{\left(\frac{k}{\rho }\right)}^{\frac{1}{m}}-1\right]}^{\frac{1}{n}}$$

The same procedure is used for φ. Equations (10) and (11) are fitted to the experimental data by the Excel Solver tool.

RESULTS AND DISCUSSION

The add-in developed is called ACC, which means an add-in to the soil compression curve. ACC combines all functions in a single window. The options for calculating PCS, CI, and the maximum σ for fixed values of ρ and φ are arranged in pages on a multipage object.

Input data is typed into the Excel worksheet, from where the data is imported by the ACC. Up to 20 load-deformation pairs can be used, which makes ACC adaptable for different compression test configurations. For a data sequence of several compressibility tests (multiple samples), the data address (cell reference of the spreadsheet) needs to be provided only once because, at each calculation, the ACC enables the automatic movement to the next column of data. This procedure can be performed in both directions (forward or backward) if the data are organized in the read pattern required by the program.

For each sample, ACC calculates and transfers to the same worksheet containing the input data the ε, ρ, φ (in all loads), PMC and IP (for Casagrande option), PCS, CI, the degree of initial saturation, and statistical parameters of curve adjustments. Keeping all data in an Excel file instead of exporting them to a specific database software is very useful because Excel also serves as a database, being widely used on most computers and offering a spreadsheet and chart editor. Thus, the database is already formed in a software that can be used for analysis, synthesis, and presentation of results.

For all calculation options, ACC also generates a chart of the procedure and presents some of the main results (Figure 2). The three curves that start below the load axis represent the first (minimum coincident with IP), the second (zero coincident with IP and minimum coincident with PMC), and the third derivative (zero coincident with PMC) of [eq. (1)]. Further details on the location of PMC and IP are shown in Figure 1. The chart can be exported as an image file or as an Excel graphic object. As a graphic object, the chart can be edited in Excel or the data provided in the generated worksheet can be used in another chart editor.

FIGURE 2
ACC window showing the use of Casagrande option with ε_i constant and equal to the initial void ratio of the sample (left) and with ε_i estimated (top right), as well as the use of Dias Junior and Pierce option (bottom right).

Unrestricted parameters of [eq. (1)] allow the analysis of the restriction effect on CC and on PCS estimation. The estimated curve with a ε_i restriction, constant and equal to the initial void ratio of the sample (Figure 2, left), shows how the restriction introduces into the adjusted CC an operational problem from the compressibility test. This operational problem is related to the difficulty of ensuring the perfect fit for the load application device on the sample surface without receiving load and suffering deformation. For the data from this example, this perfect fit might have been undetected, causing a downward “rung” in CC between the first and second ε when the first load was applied. With the ε_i restriction constant and equal to the initial void ratio of the sample, the adjusted CC is forced to pass up some of the subsequent points to the first one, decreasing PMC (66.1951 kPa) and, consequently, PCS (87.5106 kPa). Without the restriction (Figure 2, top right), the adjusted CC represents better the measurement behavior over the entire load range and results in higher values of PMC (93.7414 kPa) and PCS (121.7492 kPa). The choice of whether or not to restrict the parameter ε_i, the difference in PCS was 34 kPa, which is reflected in the soil loadbearing capacity estimation since the first one has been used to estimate the second (DIAS JUNIOR & PIERCE, 1995; KELLER et al., 2011).

When using a database with 347 compression curves, the estimation of parameters of [eq. (1)], obtained with ACC, was compared with the estimation provided by SAS statistical software (SAS INSTITUTE, 1999). In both programs, [eq. (1)] was adjusted to the situation that most requires the adjustment algorithm, i.e. estimating all parameters (ε_i, ε_f, α, n, and m).

The relationship between values estimated with ACC and SAS formed an almost perfect 1:1 line (Figure 3). This relation described a function close to the identity function (y = bx) since b value was close to 1 and a value was close to zero in all relations. This indicates that ACC estimated values similar to those estimated by SAS for all 347-compression curves.

FIGURE 3
Comparison of parameters of [eq. (1)] estimated with ACC and SAS, and relationship between precompression stress (PCS) and the parameter α.

As shown by FRITON (2001), the estimation of the parameter α requires a great precision since small changes in its value imply large changes in PCS value. This can also be observed by the relationship between PCS and the parameter α from the 347 curves used in this study, especially when α was lower than 0.01 (Figure 3). Therefore, the precision in calculating PCS with the use of [eq. (1)] largely depends on the accuracy of the estimation of parameter α. Compared with SAS, ACC met this requirement.

Running of ACC only requires that Excel is installed on the computer and that Solver tool is enabled. Instructions on how to install ACC and enable Solver are provided in a file that accompanies the add-in. The ACC also provides a help file containing instructions for organizing the input data and using the add-in. Furthermore, the ACC is distributed free and the entire programming script is available and can be modified or used as a basic structure for other programming projects.

CONCLUSIONS

The Excel add-in developed allows calculating the precompression stress and compression index by using the Casagrande method from the application of the van Genuchten equation without restrictions on its parameters, and by using Dias Junior and Pierce method in its original form or modified for void ratio.

REFERENCES

• BAUMGARTL, TH.; KÖCKB, B. Modeling Volume Change and Mechanical Properties with Hydraulic Models. Soil Science Society of America Journal, v. 68, n. 1, p. 57-65, 2004.
• CASAGRANDE, A. Determination of the preconsolidation load and its practical significance. In: INTERNATIONAL CONFERENCE ON THE SOIL MECHANICS AND FOUNDATION ENGINEERING, 1936, Cambridge. Proceedings… Cambridge, Harvard University, 1936. p. 6064.
• DIAS JUNIOR, M.S.; PIERCE, F.J. A simple procedure for estimating preconsolidation pressure from soil compression curves. Soil Technology, v. 8, n. 2, p. 139-151, 1995.
• FRITTON, D.D. An improved empirical equation for uniaxial soil compression for a wide range of applied stresses. Soil Science Society of America Journal, v. 65, n. 3, p. 678-684, 2001.
• KELLER, T.; LAMANDÉ, M.; SCHJ∅NNING, P.; DEXTER, A.R. Analysis of soil compression curves from uniaxial confined compression tests. Geoderma, v. 163, n. 1-2, p. 13-23, 2011.
• MACHADO, A.L.T.; TREIN, C.R. Characterization of soil parameters of two soils of Rio Grande do Sul in modeling the prediction of tractive effort. Engenharia Agrícola, v. 33, n. 4, p. 709-717, 2013.
• MENTGES, M.I.; REICHERT, J.M.; GUBIANI, P.I.; REINERT, D.J; XAVIER, A. Alterações estruturais e mecânicas de solo de várzea cultivado com arroz irrigado por inundação. Revista Brasileira de Ciência do Solo, v. 37, p. 221-231, 2013.
• REINERT, D.J.; ROBAINA, A.D.; REICHERT, J. M. Compress – software e proposta de modelo para descrever a compressibilidade de solos e seus parâmetros. In: CONGRESSO BRASILEIRO DE CIÊNCIA DO SOLO, 29., 2003, Ribeirão Preto. Anais… Ribeirão Preto: SBCS, 2003. 1CD-ROM.
• ROSA, D.P.; REICHERT, J.M.; MENTGES, M.I.; VIEIRA, D.A.; VOGELMAN, E.S.; ROSA, V.T.; REINERT, D.J. Métodos de obtenção da capacidade de suporte de carga de um Argissolo cultivado. Revista Brasileira de Ciência do Solo, v. 35, p. 1561-1568, 2011.
• SAS INSTITUTE. SAS/STAT procedure guide for personal computers 5. ed. Cary, 1999.
• SEVERIANO, E.da C.; OLIVEIRA, G.C. de; DIAS JÚNIOR, M. de S.; CASTRO, M B. de; OLIVEIRA, L. F. C. de; COSTA, K. A. de P. Compactação de solos cultivados com cana-de- açúcar: I-modelagem e quantificação da compactação adicional após as operações de colheita. Engenharia Agrícola, v. 30, n. 3, p. 404-413, 2010.
• VAN GENUCHTEN, M. Th. A closed-form equation for predicting the hydraulic conductivity of unsaturated soils. Soil Science Society of America Journal, v. 44, n. 5, p. 892-898, 1980.
• VOGELMAN, E.S.; MENTGES, M.I.; REICHERT, J.M.; ROSA, D.P.; BARROS, CAP.; REINERT, D.J. Compressibilidade de um Argissolo Vermelho-Amarelo trafegado e escarificado. Ciência Rural, v. 42, n. 2, p. 291-297, 2012.

Publication Dates

• Publication in this collection
  May-Jun 2017

History

• Received
  03 Sept 2014
• Accepted
  10 Dec 2016