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

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.

to assess the exact information about the shape of a distribution that is conveyed by its third and higher order moments (Landwehr, 1979). In addition, for small sample size, the numerical values of sample moments can be very different from those of the probability distribution from which the sample was drawn (Ehiorobo & Izinyon, 2013). On account of these limitations of MOM, alternative approach such as L-moments (LMO) was introduced to accurately estimate the parameters of probability distributions. L-Moment is a dramatic improvement over conventional product moment statistics for characterizing the shape of a probability distribution and estimating the distribution parameters, particularly for environmental data where sample sizes are commonly very small (Hosking, 1990;Izinyon & Ehiorobo, 2015).
Although, L-moment is more reliable compared to other conventional methods, but the underlying equations needed to obtain the L-moment parameters are usually very complex and requires an in-depth knowledge of mathematics. In addition, most of the tools (software) that can be employed in the determination of L-moment parameters such as L-RAP, DHI software are not only very expensive to acquire, they are also not user friendly. The implications are that so many researchers are not able to do much in this area of studies since they are either not able to purchase the needed software or are unable to solve the complex mathematical equations required to compute the L-moment parameters. The purpose of this research therefore, was to design an algorithm using Microsoft Excel that will help estimate the parameters of three probability distributions namely; Generalized Extreme Value (GEV), Generalized Logistics (GLO) and Generalized Pareto (GPA) using L-Moment procedure.

L-Moment Theory and Statistics
L-moments can be obtained by considering linear combinations of the observation in a sample of data that has been arranged in ascending order. Consider measurement of the shape of a distribution, given a small sample drawn from the distribution. Denote by X1:1 ≤ X2:n ≤ …… Xn:n (Hosking & Wallis, 1997;Eregno, 2014). The basic steps in the determination of L-Moment statistics are described below; Step One: Computation of probability weighted moments of distribution (pwms) Probability weighted moments is needed for the calculation of L-moment. The data must first be ranked in ascending order of magnitude, thereafter; the following equations proposed by cunnane, 1989 can thus be applied Where; X (j) represent the ranked annual maximum series in which X (1) is the smallest precipitation or stream flow data and X (n) is the largest. The parameters (b0, b1, b2 and b3) can easily be determined by using the developed Microsoft excel algorithm.
Step Two: Computation of L-Moment Values L-moment values are easily calculated in terms of probability weighted moment (PWMs). In particular, the first four L-moment values are given as follows (Hosking & Wallis, 1997).
) can easily be determined by using the developed Microsoft excel algorithm that requires forty year's annual maximum monthly rainfall or discharge data.
Step Three: Computation of L-Moment Ratio L-Moment ratio used for expressing the parameter estimates are as follows (Hosking and Wallis, 1997 (11) L-Cv is a dimensionless measure of variability. For a distribution or sample data that only has positive values, L-Cv is normally in the range of 0 < |L-Cv| < 1. Negative values of L-Cv are only possible if the at-site mean has a negative value (Sanjib, 2016;Herlina, 2015). The descriptions of the relative magnitude of variability is presented in Table 1  Table 1: Magnitude of L-Cv L-Skewness is a dimensionless measure of asymmetry, which may take on positive or negative values. For a distribution or sample data, L-skewness is in the range 0 < |L-Skewness| < 1 (CEH, 2001). The descriptions of the relative magnitude of asymmetry is presented in Table 2 Table 2: Relative magnitude of Asymmetry L-kurtosis refers to any measure of the "peakedness" of the probability distribution of a real-valued random variable. The parameters ( 2  3  and 4  ) are computed using the formula below (Hosking & Wallis, 1997;Gubareva & Gartsman, 2010).
The parameters ( 2  3  and 4  ) can easily be determined by using the developed Microsoft excel algorithm that requires forty year's annual maximum monthly rainfall or discharge data.

Advantages of L-Moment
The main advantage of L-moment over conventional moments is that L-moments, being linear functions of the data, suffer less from the effects of sampling variability and are more robust compared to conventional moments in handling outliers. In addition (Hosking & Wallis, 1997). Some of the underlying simplicity of L-Moments are; i. L-moment is based on linear combination of data that have been arranged in ascending order of magnitude. It provides an advantage as it is easier to work with, and more reliable since it is less sensitive to outliers. ii.
The method of L-moment calculates more accurate parameter than method of moment (MOM) technique especially for smaller sample size. iii.
MOM techniques only apply to limited range of parameters, whereas L-moment can be more widely used, and are also nearly unbiased Civil and Environmental Research www.iiste.org ISSN 2224-5790 (Paper) ISSN 2225-0514 (Online) Vol.11, No.9, 2019 46 iv.
L-Moment allow for the generation of ratio diagrams which are helpful in identifying the distribution properties of highly skewed data v.
L-Moment is a dramatic improvement over conventional product moment statistics for characterizing the shape of a probability distribution and estimating the distribution parameters, particularly for environmental data where sample sizes are commonly very small vi.
In practice, an in-depth knowledge and accurate estimation of the L-moment ratios of L-Cv (Ʈ2), L-Skewness (Ʈ3) and L-kurtosis (Ʈ4) is a key determinant in quantifying the success of the regional frequency analysis in computing quantile estimates for selected stations (Hosking & Wallis, 1997).

3.0: Development and Execution of the Algorithm
The Microsoft Excel algorithm was design to capture three probability distributions, namely; Generalized Extreme Value (GEV), Generalized Logistics (GLO) and Generalized Pareto (GPA). The GEV distribution is a flexible distribution which has been found to fit flood and rainfall extremes in a variety of environments. The underlying equations including the quantile function (xp) corresponding to the non-exceedance probability (p) and the return period (T) corresponding to the non-exceedance probability (p) have been incorporated into the algorithm. The probability density functions and or cumulative distribution functions, range and moments for the selected distributions captured by the algorithm are presented in Table 3 and 4 respectively while the goodness of fit statistics captured by the algorithm is presented in Table 5 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. In addition, RRMSE can also provide a better picture of the overall fit of a distribution as it calculates each error in proportion to the size of observation thus helping to eliminate or reduce the effects of bias commonly associated with hydrological data (Tao et al, 2008).
Civil and Environmental Research www.iiste.org ISSN 2224-5790 (Paper) ISSN 2225-0514 (Online) Vol.11, No.9, 2019 To develop the excel algorithm, the underlying mathematics of L-moment were employed. The complex equations were first digested and rewritten in a more simplified format that is usable by Microsoft Excel. Two algorithms were developed, namely; algorithm one and algorithm two. Algorithm one uses the ranked discharge or precipitation data (Xi) (40 years annual maximum data) as the input data to calculates the following parameters: i. Variance ii.
Standard deviation iii. Skewness iv.
Coefficient of variation (CV) vi.
Shape parameter (k) of GEV, GPA and GLO probability distribution x.
Scale parameter (α) of GEV, GPA and GLO probability distribution xi.
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. The predicted rainfall or discharge values (yi) ii.
Quantile estimates at various return periods iv.
Exceedance and non-Exceedance probabilities

3.1: Computation of L-moment parameters Using Algorithm One
The following steps are involved in using algorithm one to compute the parameters of GEV, GLO and GPA probability distributions. i. The algorithm is available on request. Table 6a and 6b shows a section of the algorithm ii.
The algorithm requires 40 years annual maximum data (precipitation or discharge) which must be ranked in ascending order of magnitude. The data must be prepared in excel, copy and paste on cell B2 -B41 of Algorithm one iii.
Descriptive statistics of the data presented to the algorithm will be gotten immediately from cell D4 -D10 iv.
The L-moment values ( The L-moment ratio values ( 2  3  and 4  ) will be gotten immediately from cell M10 -M12 vii.
The parameters of the generalized extreme value probability distribution (GEV) which include; Shape parameter (k), Scale parameter (α) and Location parameter (ξ) will immediately be computed and presented in cell O3, O5 and O6 viii.
The parameters of the generalized logistics probability distribution (GLO) which include; Shape parameter (k), Scale parameter (α) and Location parameter (ξ) will immediately be computed and presented in cell O16, O18 and O17 ix.
The parameters of the generalized pareto probability distribution (GPA) which include; Shape parameter (k), Scale parameter (α) and Location parameter (ξ) will immediately be computed and presented in cell O26, O28 and O27

3.2: Predicted values and Quantile Estimates based on selected Return Period Using Algorithm Two
The following steps are involved in using algorithm two to determine the quantile estimates for selected return periods based on the probability distribution. i. The algorithm is available on request. Table 7a and 7b shows a section of the algorithm ii.
The same 40 years annual maximum data (precipitation or discharge) used in Algorithm one is also needed to run Algorithm two. Go to cell B2 -B41 of Algorithm one where you have your 40 years annual maximum data (precipitation or discharge) copy and paste on cell E2 -E41 of GEV, GLO and GPA in Algorithm two iii.
Copy the computed parameters (Shape parameter (k), Scale parameter (α) and Location parameter (ξ)) of each distribution from algorithm one. Replicate them to 40 years data and paste in cell F2 -F41, G2 -G41 and H2 -H41 of GEV, GLO and GPA in Algorithm two iv.
The predicted discharge or precipitation values of generalized extreme value (GEV) will be gotten from 49 cell N2 -N41 of Algorithm two; that of generalized logistics (GLO) will be gotten from cell O2 -O41 of Algorithm two and that of generalized pareto (GPA) will be gotten from cell N2 -N41 of algorithm two. v.
The goodness of fit statistics for selecting the probability distribution that best fit your data, namely; root mean square error (RMSE), relative root mean square error (RRMSE) and maximum absolute deviation index (MADI)) will be gotten from cell R3, V3 and X3 of algorithm two for generalized extreme value distribution (GEV), cell S3, W3 and Y3 of algorithm two for generalized logistics distribution (GLO) and cell R3, W3 and Y3 of algorithm two for generalized pareto distribution (GPA) vi.
Cell AE2 to AE11 of Algorithm two gives the computed quantile estimates for the generalized extreme value distribution (GEV), generalized logistics distribution (GLO) and generalized Pareto distribution (GPA). vii.
The cumulative probability of non-exceedance is also computed in addition to the graphical visualization of your data with respect to GEV, GLO and GPA probability distribution.    (Ikhuoria, 1987). It has two main seasons, wet and dry; from April to November and November to April respectively, with an annual mean rainfall of about 2095mm. In matters of hydrogeology, Benin City lies on the Benin Formation, with an aquifer of mean dept of 136m. Schools, hospitals, markets and cemeteries are among the social services provided in the City. Figure 1 shows the digitized map of the study area

4.2: Data collection
The data used for this study was collected from the Nigerian Meteorological Agency, Oshodi; Lagos State, Nigeria. The data includes monthly precipitation data for 40 years spanning between; 1974 to 2013. The data were then sorted to obtain the annual maximum precipitation records.

5.0: Results and Discussion
The time series plot of the data presented in Figure 2 shows the presence of seasonal variability since rainfall depth varies within the period understudy as some years experienced extreme precipitation compared to others.

Figure 2: Time series plot
On whether the rainfall data used in this study are from the same population distribution, homogeneity test was performed using hydrological software (RAINBOW). Result of the test presented in Figure 3 shows that the Civil and Environmental Research www.iiste.org ISSN 2224-5790 (Paper) ISSN 2225-0514 (Online) Vol.11, No.9, 2019 52 data used is homogeneous.

Figure 3: Homogeneity test of data
For a homogeneous record, the rainfall data points normally fluctuate around the zero-center point in the residual mass curve as observed in Figure 3. The descriptive statistics of the rainfall data and the computed probability weighted moment statistics (b0, b1, b2 and b3), the L-moment values (ƛ1, ƛ2, ƛ3 and ƛ4) and the Lmoment ratio values (Ʈ2, Ʈ3 and Ʈ4) were obtained from algorithm one and presented in Table 8  Table 8  is constrained by 3  to be no lower than -0.25 (Eslamian and Feizi, (2007). The parameters of location (ξ), scale (α) and shape (k) of the selected distributions estimated using algorithm one is presented in Table 9 Table 3. The predicted annual maximum precipitation records based on L-moment using the three-probability distribution model, namely; GEV, GLO and GPA was obtained from algorithm two and presented in Table 10 Vol.11, No.9, 2019 lowest RMSE, lowest RRMSE, lowest MADI, lowest MAE and highest PPCC was assigned a score of 3, the next was given the score 2, while the worst was given the score 1. The overall score of each distribution was obtained by summing the individual point scores and the distribution with the highest total point score was selected as the best fit distribution model. The scoring scheme and the overall ranking of the distributions models at the stations based on the goodness of fit tests is presented in Table 12  Table 12: Scoring and ranking scheme for selected probability distribution models  Test Criteria  Distribution Scoring  GEV  GLO  GPA  RMSE  1  2  3  RRMSE  1  3  2  MADI  1  3  2  MAE  1  3  2  PPCC  2  3 1 Total Score/ Rank 6 (3rd) 14 (1st) 10 (2nd) Based on the result of Table 12, generalized logistics probability distribution (GLO) with the highest total score of 14 was selected as the best probability distribution model for analyzing annual maximum rainfall series in Benin City followed by GPA and then GEV. The quantile estimates (Qt) based on 2, 5, 10, 20, 50, 100, 200 and 500 years was also obtained based on L-moment procedure using algorithm two and results obtained is presented in Tables 13 Table 13: Computed quantile estimates based on selected return periods for GLO using algorithm two The graphical visualization of the observed and predicted annual maximum precipitation for GLO was obtained from algorithm two and presented in Figure 4

Figure 4: Observed versus predicted precipitation based on GLO distribution
The computed coefficient of determination (r 2 ) between the observed and predicted precipitation was observed to be 0.9793 for generalized logistics distribution (GLO). Based on the computed coefficient of determination, it was concluded that generalized logistics distribution had a better fit of the annual maximum

Conclusion
This paper gave a detail description of the current method of statistical parameter estimation of selected probability distribution model, namely; Generalized Extreme Value (GEV), Generalized Logistics (GLO) and Generalized Pareto (GPA) probability distributions -the L-moments method. A simple to use Microsoft Excel Algorithm have been developed for estimating basic descriptive statistics such as the sample mean, variance, standard deviation, skewness, kurtosis, and coefficient of variation. 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 and MADI). The algorithm will not only find use in the practice of engineering hydrological computation, it will also help design engineers in estimating the magnitude of peak rainfall/discharge for various return periods.