Environmental Engineering Reference
In-Depth Information
AB
C
D
E
F
G
H
2
3
4
5
Spreadsheet Template for Calibrating a Normal Distribution
Parameters to
be estimated
µ
σ
6
18.052
5.014
Log-likelihood
7
8
Oberved data
Eq. (4.3)
Test No.
φ
(°)
f
(
d
i
|θ)
ln[f
f
(
d
i
|θ)]
L
(θ|
D
)
9
1
11.86
0.037117612
-3.293663704
-75.78058016
10
2
14.04
0.057769078
-2.851301635
11
3
13.5
0.052693349
-2.943266032
12
4
10.2
0.023347321
-3.757273035
13
5
11.31
0.032221271
-3.435128467
14
6
12.95
0.04741316
-3.04885546
15
7
12.41
0.042246646
-3.164230304
16
8
13.5
0.052693349
-2.943266032
17
9
12.95
0.04741316
-3.04885546
18
10
15.64
0.070870141
-2.646906075
19
11
16.17
0.07415113
-2.601649971
20
12
17.22
0.078474352
-2.544983438
21
13
20.81
0.068391957
-2.682500042
22
14
20.81
0.068391957
-2.682500042
23
15
25.64
0.025317143
-3.676273531
24
16
22.29
0.055664491
-2.888412834
25
17
24.7
0.033036205
-3.410151183
26
18
26.1
0.021942924
-3.819310554
27
19
22.78
0.051007472
-2.97578315
28
20
19.8
0.074870812
-2.591991157
29
21
17.74
0.079408061
-2.53315539
30
22
20.81
0.068391957
-2.682500042
31
23
20.3
0.071954129
-2.631726466
32
24
20.3
0.071954129
-2.631726466
33
25
27.47
0.013634258
-4.29516969
34
35
36
37
Notes:
(1) The setting in Solver is "Maximize Cell G9 by changing the values in Cells D6 and E6".
Figure 4.1
Spreadsheet template for calibrating parameters of a normal distribution.
Assume that the observed data
d
1
,
d
2
, …, and
d
n
are statistically independent. The like-
lihood and log-likelihood functions in this problem can be formulated by substituting
Equation 4.7
into
Equations 4.1
and
4.2
,
respectively. By maximizing the likelihood, the
optimal values of θ can then be determined.
Figure 4.2
shows a spreadsheet template for
estimating the values of θ based on the maximum likelihood principle. With Excel Solver,
the maximum likelihood estimate of θ is found to be {μ
1
, σ
1
, μ
2
, σ
2
, ρ} = {156.44 kPa,
122.23 kPa, 18.05°, 5.01°, −0.41}.
Search WWH ::
Custom Search