Simple to Use Microsoft Excel Template for Estimating the Parameters of Some Selected Probability Distribution Model by Method of L-Moment

Idowu Rudolph Ilaboya

doi:10.7176/cer/11-9-05

Abstract

The focus of this research was to design a simple to use Microsoft excel algorithm that will aid in the estimation of the parameters of generalized extreme value probability distribution (GEV), generalized logistics probability distribution (GLO) and generalized pareto probability distribution (GPA), calculate the predicted rainfall/discharge based on L-moment procedures and compute the quantile estimates at various return periods.The algorithm was design based on the underlying mathematics of L-moment and has the capacity to handle forty (40) year’s annual maximum series of either rainfall or discharge data which must first be ranked in ascending order of magnitude. Basic descriptive statistics such as the sample mean, variance, standard deviation, skewness, kurtosis, coefficient of variation have been built into the algorithm. Other exciting features include; the computation of Probability weighted moment parameters (b0, b1, b2 and b3), L-Moment values (ƛ1, ƛ2, ƛ3 and ƛ4) , L-Moment ratio values (Ʈ2, Ʈ3 and Ʈ4), and goodness of fit statistics (RRMSE, RMSE, MAE, MADI and PPCC). Others include; the shape (k), scale (α) and location (ξ) parameters of GEV, GPA and GLO probability distributions. To test the performance of the algorithm, forty (40) year’s annual maximum rainfall data from Benin City was used. Basic time series analysis such as test of normality, test of homogeneity and outlier detection was conducted to ensure that the data used are adequate and suitable.Results obtained revealed that generalized logistics probability distribution GLO was the best fit distribution model for analyzing the annual maximum rainfall series at the study site. The predicted rainfall quantile magnitude (Qt) based on the GLO model ranges from 425.877mm at 2years return period to 762.759mm at 200years return period. The coefficient of determination (r 2 ) for the observed versus predicted rainfall based on the best fit model was observed to be 0.9793. It was thereafter concluded that L-moments and L –moment ratios are useful summary statistics for analyzing rainfall data. Keywords: L-moments, probability distribution, normality test, goodness of fit statistics, coefficient of variation. DOI : 10.7176/CER/11-9-05 Publication date :October 31 st 2019

Highlights

Estimation of extreme flood discharge of known return period is paramount in the design of hydraulic structures such as culverts, dams, bridges and drainage systems
Owing to the stochastic nature of the hydrologic phenomena that governs extreme flood discharge, it is fundamental that we investigate most hydrologic processes such as rainfall and droughts by analyzing their records of observations (Ehiorobo & Izinyon, 2013)
Effective analysis and determination of extreme flood discharge requires the use of statistical frequency analysis or fitting of probability distribution to the series of recorded annual maximum discharge (AMD) (Vivekanandan, 2015; Sharma & Singh, 2010)

Summary

L1 b0

Microsoft excel algorithm that requires forty year’s annual maximum monthly rainfall or discharge data. Root mean square error (RMSE), relative root means square error (RRMSE) and maximum absolute deviation index (MADI) were selected since they can adequately assess the fitted distribution at a site. They possess an added advantage of being able to summarize the deviation between observed precipitation and predicted precipitation. Location parameter (ξ) of GEV, GPA and GLO probability distribution Algorithm two uses the 40 years precipitation or discharge data including the calculated shape parameter (k), scale parameter (α) and location parameter (ξ) from algorithm one as input data to calculates the following parameters: i.

3.1: Computation of L-moment parameters Using Algorithm One

Table 6b