J. Jpn. Soc. Soil Phys.
(¼῎9No. 30, p. /1 0- ,**.

   *

Numerical inverse procedure to estimate soil hydraulic properties using Excel
Kosuke NOBORIO** Faculty of Agriculture, Iwate University, -ῌ+2ῌ2 Ueda, Morioka, Iwate *,*ῌ2//*, Japan
Abstract
There is a big demand to predict water movement and solute transport in soil not only inagricultural but also in environmental ﬁelds.

To predict water distribution in soil, soil hydraulicproperties, i.e. a water characteristic curve and hydraulic conductivity, should be known in priori.
Measurement of soil hydraulic properties, however, is usually time consuming and laborious
work.

Recent improvements of computing power on PC enabled us to inversely estimate

hydraulic properties using a numerical simulation model along with experimental data.
Spreadsheet-type software, which has a macro language and a solver, is also popular. In thisstudy Excel was used to numerically simulate soil water movement using the ﬁnite di#erence
method written in Visual BASIC as a macro function. The solver function of Excel successfully
worked with the numerical model to estimate best-ﬁtted values for soil hydraulic properties bycomparing simulated results with temporal changes in soil water content measured.

The tempo-ral changes in water content were measured with time domain reﬂectometry (TDR) at ,* cm below
the soil surface during water inﬁltration into loamy sand.

A water characteristic curve withparameters inversely estimated reasonably agreed with that with parameters experimentally
determined.

Saturated hydraulic conductivity inversely estimated also agreed well with thatdetermined by an experiment.

Excel would be alternative software to estimate soil hydraulicproperties with an inverse procedure.
Key words : inﬁltration, hydraulic conductivity, water characteristic curve, ﬁnite di#erence
method (FDM), time domain reﬂectometry (TDR)oUp qrsGs(tEuv$ p$+    ῎89swG 2 Simunek +:+332@ Hydrus_+D x

7yῌzapjkYlRjmWno   !"#$%&'()*!+,-./U{C/0ῌ#1 2  ῎89(wG}~ie2 i!i Hydrus_+D  

3454$67( ῎89 :89;<=(bcd|` |(RjmW i/3/>?@ 1+!A.BC/ %DῌE1 2 FE

` (# ῍(1/noUg ῎89(GHIJIEK! (ELMN E#1 2#1 2L) U(p$ljR(SOP(QRSTUVWXYRZ([\]^_3 %¡¢i/` £ ¤ ¥¦{Ce?

/` HabcdaE1 2 bcda# efsqr§SU¨R©ªp «<¬uv , ­®/.qr :¯° bcd@ E±ª#1 2 Wraith  Or :+332@

῏(JINghGi jkYlRjmWn* ²H³´´µ ¶*,*ῌ2//* ·¸£¹ -ῌ+2ῌ2 : º» = >?
89;< t a TDR ab c 30 µ o,**.p58

       !"#$% &'()*+,
1+-./

0

Ki => ir+  i xiRX$@A omp rr => i  iz+ xiRB2
Komῌminpiz+

5% B2 ῏r
KiqK ῍῎ῐ῎῍῏ῌ῎῍ ῐ234 5673% #8# 9 :;ῌ<=>?@A.#$%

 #  B2 CDE8

῍FCG5% H1IFJ '()*+, +-./!"# FKLMN
TU $VWX3 FC%Y
8ZI8&['(C5% 55nwCD#$/0s1 ῏

OP#$/0s1 , EFDwx#)\] ^_KL
*`Gabc0 H
1Q # Cd5 $+e%JO#$#C

1I#a, n, Ks #$% H^_$VWX

3 FG#$%
0 'L #5 a, n,Ks  #$Go v*vp # ^_KL Visual Basic Editor 8S,    HIJ# #$%H water-ῌ moveFIK:#$%X+ fg,h-iRjkl

.n3 oSimunek S +332p%(q( ῍(h῏qrrKrKῐ(t(z ῎(zv * l  omp K  2  ῌom ῌ minp t  w xominp z 3.48S5n omp 5% X$  ῍ ῎ 1I

van Genuchten .n3 ovan Genuchten, +32*p%

KoqpqKsy {+ro+ry

῍+ῌm m ,p |῎HH yyqoqrqrpῌoqsrqrp .n3}67
8 qs 67/0s1 om-ῌm-p qr ~9/0s1om-ῌm-p Ks 672

 G,, G-, G. T33N¤ watermove 8S

H3S_¥# OVW¦§ oP¤4 G, _8
S#OVWV theta_iqActiveSheet. RangeoG,p¨Value ©Ftheta_i  G, _#J@

% ªQR5C KL8S£*+O
P#2S2ActiveSheet. RangeoG,p¨
Valueqtheta_i 34 theta_i «Tn3 FOP#£*+ G, _©2¬HC2+o+z῕ah῕npm
*./

#$'L

!Lῑ+  % 'L M¡ q :;#5 qint i1¢ qs  qr #£*+_HH q /0s1 om-ῌm-p h K+jtuyohpq

 o ῏p 'L ^_KLMN5 Visual BASIC #$% 0 #OP1Q8 R34 S F%m1

ῐomῌminp a, n, mq+r+ῌ

n Ga 5%  ῌ :;<

p% nSwatermove  OPC;­E3  5S8®¯£*+ E UVW°
J@# iF$s1D# Dw±OPw±Cª$w°fH3S_8SOVW¦§%XF watermove  ²Dw±iRD

#OP# , EOP# FDwx H
3S , EY³#$% HY³ , E´watermove## £*+Z% )\] £*+_[µ G0/ qwatermoveoC,,qtztqqitzit ῑr῍hir+rhi ῏ r
Kiz+ rKiz+ῐzῒ ῎ z

῍hirhiz+ z+῏ΐ῎ zῐ῔C-, C/p ©F ῏

# ·n3_ C,, C-, C/  ¸¹2a, n, Ks :;#T33N¤ 8S Y % HH qtzt=> i iRt wx/i0s1 om-ῌm-p qit => i iR/0s
1 om-ῌm-p t OPwxx

watermove ¶¦F _[µ G0/ Y³ , E´#C\n3% nº$%]ῑ+ ^»4 %  _ominp z =>x

G0/ \n3$Y³ , E´#_$¯_? omp hir+, hi, hiz+ T33=> ir+, i, iz+ iR C,, C-, C/ N¤S3 F a, n, Ks #`¼a¤: ¸v¹2y' *<=./0%¼#   watermove  z{| 9 Excel ,*** y !

 ,  ! "#$ῌ ῑΐ῏῎῍ῌ$% , &'&()*+ a,i} $~

./01lm<2ῌ +  >n, Ks ,-./0$1!

$23+4%56789 :; <=>?@AB59.C. qint Dm-ῌm-E F

?@2]#!  9 kῌ+>?@\

56ῌOP782y'! $~9 qint qs M*

GHI#!  <=>?@JK DzL* cmE qr %$9!  kῌ,

*+<=./0%lm<2]#! >?@~

MNOP789 QRST +.* cm %U.V Dh

'L+.* cmE 2'WX! F <=>?@YK kST ,* cm %V MHC.

MNOP789 Z[\%>?@2]%gh i2 + c! 9 `%DzLL cmE# (hῌ(zL* !

^_`%$9 .

TC.%gh i~"$1I#>?@YK ab %c MH%d e!

1! ' Vf ,* cmii} >?@~

MNC.%ghi2j  *+ >?@\2 LL-*.*~ 9 Y%=l<2 , mmcm  Dkῌ+E!

{22y ! , mm {

a, n, Ks %56$9 lm< :9no *.+%S)2i¡¢>?@ D£ +* cm, \D+ῌcmE +.2 *.+ DcmῌminE  =l< :9n ,0 cmE

o *.+ D+ῌcmE +.. *.+ DcmῌminE ! %¦§ ¤f9 +.*+ Mgῌm- '!  ha, n, Ks %Y[$9 no *.*+ D+ῌcmE +.+ * Dcmῌ%B¨C.9 *.*/, Dm-ῌm-E 1'! >?@~

minE  J[$9p#S'!

9 h%B¨C.2 qint I# ~©h

9 +Gbite RAM  -* Gbite q PC 2y'! OSFig. +ῒ῍+k MH + cm %U.V*+ ¯°±²

¸v¹%zº¡»The solver function of Excel.

*$%78

Calculating conditions for numerical simulation using the explicit ﬁnite di#erence method>?@\LDcmElm<=l<$9 qint %h ª«¬TH­®$2!

9 Windows ,*** 2  kῐ῍+Table +MN TDR  *"#$2 qs  Dkῌ+E! qr %%1r#stuvM* Pentium. ,./- GHz CPU 2wx¤f¥F *+ ῌ! n-*4*-*4*hcc ´µ¶³t DminE³zDcmE*4**+*4**+*4/*4/

C.
qsDm-ῌm-E*4.**4//

OP78·C. 56C.
qrDm ῌm E qintDm ῌm E >?@JK >?@YK*4*/*4*/*4*0*4*/,hL+4* cmhL+4* cm(hῌ(zL*(hῌ(zL*M9* » 30 ¼ ,**.$

60

ΐῌ,Table ,v=v³LM%*(z?@v

Known and experimental values for soil hydraulic properties and inversely-analyzed
valuesa´µ ev
M´ WIJ



$v$

nKs

+ῌm$+,4.
.4.0$

v

z?@v
mῌmin$,4.-¶+*·+4+.¶+*·-,4,2+4-3a+ῌm$+.4.
04/.

n,4/*,4*-Ksmῌmin$,4/1¶+*·+4,0¶+*·-   ,* cm  ,  TDR  ! +.0mm "# +./ cm  2.0 cm$ %&' ()*%+ +, TDR ,- ./0/12345.16 785+/*,C$ 9 : 45.16;<=>?@- +331$ ABCDEFG6HIJ9 TDR KLM%

,

N- ,**-$ OPQRSTP
UV MWIJX Y%Z[V WIJ\(%Q]4^ _%`:: + a" V %bKc%de,
: f9`gh=ijKck
lm/n.Eop5=*% Nd TNd ῌ QqTL van Genuchten !-rῒῌ,´µ%  ev ¦L ,* cm "Q*% xy +u ¸¹-evv
-z?@Kcºr%*(9`vuFig. ,῍, qTLst -ῌ ῏῎῍ῐῑiu evw῍, - ,* cm "Q,*%xy+=

Temporal changes in volumetric watercontent at ,* cm deep in a sandy soil.
Open circles represent values determinedby a numerical experiment, and a solidline represents simulation results using
hydraulic properties estimated by an inverse analysis.

z?@K`{!-rT*%  +AqTL x|y" +2}~#r

xy$Y*%  +- ,v=v% Pr& +:Q,KPLw῍/ -z?@KcU-+%*(v9` vQL a, n, Ks =z?@K`)P% ({= .

Kc¡% ( a, n, Ks '῍, q de&{q ¢% £Q-%-'$&

PL=AL  Tde9`%

PLA /¤0% £Q-1 +*¤+** 23"v({HI)P'Aw῍- QLA¥Y`PL T- 0% £ ¦L*%

HIz?@K`%*(vAxy +,86A4§PLr -¨Y

!QL=AL

z?@AQYP=~©LQ-YP=ª«iiu WIJTL  ¬evw῍. - TDR Q,*% xy +=z?
@K`{!-r*%xyst 5z?@-X Y­"®¯=ª«TL== w῍. st+q,* cm " ¦Lz?@KL!-r-!QL %- #y"=TK`h~

=ª«TLQ ghῌij~=ª«

TLY*%  + V+Y*%

TLf°ῌ6T7KL±²A8©=Y` : cdef'A:N^_ῐῌ-}~^6QRW() +WFig. -Comparison of water characteristic
curves for a sandy soil between knownvalues and values estimated by an inverse analysis. Solid squares represent
experimental values, and a solid line
represents values estimated by an inverse analysis.

61

ῐῌ/_}^6QRW() !+ W ! van Genuchten <+W

Fig. /

Comparison of water characteristic curves
for loamy sand between experimental
values and values estimated by an inverseanalysis. Solid squares represent experimental values, a dotted line represents
curve ﬁtted to the van Genuchten equation
with the experimental values, and a solidline represents values estimated by aninverse analysis.



ῌ,  a, n, Ks!"#$%&'()  !
(*+ an ', +./ -./0 12+ 34

5%& Ks 6

!78019

!:;< a, n, Ks =ῌ, '>?@ 'A:BC ,* cm DEFGHIJKLMTDR N$HIJKLM'Oῌ0

PQ BC27 $ , . RS0TUVW7X
Y Oῌ.

Oῌ0 '()Q + ,* cm DEF TDR N:$HIJKLM'Z[ῐῌ.

_}^RTU !FG ,*cm DEHIJKLM !+W;<'A:BC

Fig. .

Temporal changes in volumetric watercontent at ,* cm deep in loamy sand.
Open circles represent values determined a column experiment, and a solidline represents simulation results using
hydraulic properties estimated by aninverse analysis.

\]+ N:^_` 8 ab`.ῌ ῎ ῍ ῏
cdef'A:N^_N'gh+ iN7jk`l 7m: TU!F`DEFGHIJKLMn7$ opqrs` 7:+ TDR N
t'A:uvB$'wbl xyz{7|G » 30 ¼ 2,**.962   H>I:JCK  !"#$%&,LMNJ*OPQR+!"#$%&'()*+ ,*- ST U .*/01 2+ 3456% -7Z a, n, Ks [\.]^:]_`7a b/ 89 :;"*+ <=>?@ 2+3219 *AB,C:(DE>DῌFG@

V WI-XY*c\" d*XYef^C:(gh i](^jk.*+ῌ ῑ ῐ ῍<=>?@ῌlmn> 2aῌopq9 2+3219 : rH
G>st s uvwxyz{|}~ ~  2,**-9 :  TDR c\~a ῌ 3- : /1ῌ0/, 3/ : 3..

ῌK. J. Dm5ῌJ.L. m@> 2+3319 :G>4)C"W TDR 5~   +22 : +,3ῌ+-/.

Simunek, J., O. Wendroth and M. Th. van Genuchten(+332) : Parameter estimation analysis of theevaporation method for determining soil hy῎ῌ0 ­®¯°± ,* cm ²8UV W~ ³´µE¯ ¶¯C-·T ¸ 2ῌ, ¹º9
I-~Fig. 0¬

Temporal changes in volumetric watercontent at ,* cm deep in loamy sand.
Open circles represent values measured
with a column experiment, and a solidline represents simulated values withhydraulic properties determined bylaboratory experiments (see Table ,).

῏ῒῌ+Appendix +draulic properties. Soil Sci. Soc. Am. J., 0, : 23.ῌ3*/.
van Genuchten, M. Th (+32*) : A closed form equation
for predicting the hydraulic conductivity of unsaturated soils. Soil Sci. Soc. Am. J., .. : 23,ῌ232.
Wraith, J.M. and D. Or. (+332) : Nonlinear parameter
estimation using spreadsheet software. J. Nat.
Resour. Life Sci. Educ., ,1 : +-ῌ+3.
 ¡¢£ : ,**- ¡ 3 ¢ + £¡¢£ : ,**- ¡ +, ¢ - £¤ ¥watermove¦ §n¨E©5ª«Computer program list and explanation for the function “watermove” : 

63