Reliability Modelling through the Three-Parametric Weibull Model Based on Microsoft Excel Facilities

Abstract

The paper aims to capitalize on the new features that are offered by the Microsoft Excel calculation program for reliability modeling, using the Median Ranks estimator that is calculated directly with the BETA.INV function, not estimated by various algebraic estimators, as is generally the case. Starting from this first step, a method of modeling reliability is elaborated through the three-parametric Weibull model that is based exclusively on this software, which is accessible to anyone and can be used even in the case of online learning, which is widespread in recent years due to the pandemic situation. The probability plotting method is applied, using the Median Ranks estimator that is calculated directly with the BETA.INV function for a probability equal to 0.5. A flowchart is made for the proposed method, which could be easily translated into a calculation program. By representing in logarithmic coordinates, we determined the Weibull models for different values that were initially adopted for the location parameter: using as a criterion the coefficient of determination that was obtained using the trendline function for the linear model, it was possible to identify, by successive tests, the optimal value of the location parameter—for which the three-parametric model has a good likelihood. By the proposed method, this value can be found following this iterative process. So, based on the current facilities of the Microsoft Excel program, a precise and easy-to-apply method has been achieved, through which an appropriate three-parametric Weibull model can be identified.

1. Introduction

The three-parametric Weibull model is considered the most suitable for reliability modeling, but its identification requires laborious methods, which is why the bi-parametric Weibull model is frequently used [1]. Thus, only bi-parametric Weibull models can be identified in general [2,3,4]—even in the case of dedicated software such as Weibull++ [5,6], and operating software allows computation with only bi-parametric Weibull models, such as Windows Microsoft Excel in Microsoft Office package [7].

But in recent years the usual MS Office computing programs have been supplemented with new features, including mathematical functions such as the BETA.INV function, which was introduced in 2010 [8], which allows the development of methods for identifying a three-parametric Weibull model.

Since conducting reliability studies through common computer packages, accessible to all, is very important (including in the case of online education, widespread in recent years due to the pandemic situation), this paper aims to capitalize on these opportunities that are offered since 2010 by Microsoft Excel [9,10] to develop a method for identifying the three-parametric Weibull model. Thus, along with the two parameters of the bi-parametric Weibull model (the shape parameter and the scale parameter), the objective of the paper can be expressed even more concretely: based on the actual Microsoft Excel program, to identify a method by which the third parameter can be identified—the location parameter—and, thus the three-parametric Weibull model.

Various methods can be used to obtain the appropriate mathematical model for the analytical description of a data distribution [11,12,13]: analytical methods—the least square method (LSM), the maximum likelihood method (MLM) and the method of moments (MOM), [14,15]—or graphical representation.

The most appropriate way to develop a method for identifying the three-parametric Weibull model based on the Microsoft Excel utility is the graphical path, because this utility contains the linear regression function through which the Weibull function can be linearized. Thus, using as a variable the failure times we can identify the bi-parametric Weibull model (for which the location parameter is equal to zero), but we can do more than that: using as a variable the failure times adjusted with the location parameter (a non-zero value), a three-parametric model is obtained. As a result, research is being developed in this direction, which will be presented below.

Thus, the main steps that will be taken are:

• Estimating the function of distribution of failure times using the BETA.INV function;
• Identification of the bi-parametric Weibull model by the known method of linear regression;
• Identification of the optimal location parameter—for which the resulting three-parametric Weibull model has the maximum coefficient of determination R;
• Argumentation the efficiency of the proposed method, based exclusively on the facilities of the Microsoft Excel program, by the superior likelihood of the three-parametric Weibull model that are obtained compared to the bi-parametric model.

2. Methods and Results

2.1. The Three-Parametric Weibull Model Parameters

The three-parametric Weibull model is defined by the following expression for the reliability function or the survival function [16]:

$$R\left(t\right)=e^{-{\left(\frac{t-\gamma }{\eta }\right)}^{\beta }}$$

(1)

or by the function of unreliability, also called function of distribution of the time of good operation:

$$F\left(t\right)=1-R\left(t\right)=1-e^{-{\left(\frac{t-\gamma }{\eta }\right)}^{\beta }}$$

(2)

where:

-

γ—location parameter or position parameter, this being a constant value that defines when the variation of the survival function R(t) starts.

-

η—scale parameter, which characterizes the extent of the distribution on the time axis. So, in the particular case when (t − γ) is equal to η, this parameter can be highlighted; because R(t) calculated with the relation (1) will be:

$$R\left(t\right)=e^{-{\left(\frac{t-\gamma }{\eta }\right)}^{\beta }}=e^{-{\left(\frac{\eta }{\eta }\right)}^{\beta }}{e}^{-1^{\beta }}=e^{-1}=0.368$$

(3)

Which means that the scale parameter represents the time that is measured from the moment $\gamma$ to whom 63.2% of products have failed. Therefore, this parameter expresses a characteristic operating time. As a result, the parameters γ and η are expressed in units of time and can be highlighted graphically (Figure 1).

-

β—shape parameter, is dimensionless and is the parameter that determines the variation of the variation curves for the reliability indicators.

2.2. Choosing the Optimal Estimator for the Distribution Function F(t)

Obviously, when the Weibull mathematical model for product reliability is known, the previous analytical relationships are used. But when the model is not known, obtaining it requires the calculation of one of the reliability indicators through the statistical processing of the values for failure times, and then, the modeling of the distribution of these singular values through a continuous mathematical function as appropriate as possible.

Due to its cumulative character, the most frequently used indicator for modeling reliability is the unreliability function or the distribution function F(t), which expresses the probability that the product will fail at the time t.

Having a set of values in ascending order with the values of time and at which the n products failed, the values F(t_i) can be estimated with various computational relationships.

Dedicated calculation programs for reliability analysis generally have two estimators for the distribution function: the Median Ranks estimator and the Kaplan–Meyer estimator, usually the default one being the Median Ranks estimator [17,18], which leads to the most probable results.

The Median Ranks method consists of estimating the unreliability for each failure time by determining the median rank for each failure.

Thus, the median rank MR_i is the value of the probability of failure of i products out of the total of n products, F(t_i), for which the time t_i represents the quintile of 50%. That is, MR_i estimate for F(t_i) represents the value for which at that moment t_i the probability that the true value is higher than F(t_i) is equal to the probability that the true value is lower than F(t_i)—therefore equal to 0.5.

The F(t_i) values result, according to the binomial law (Bernoulli), from the relation:

$${\displaystyle \sum }_{k=i}^n\left(\begin{array}{c}n\\ k\end{array}\right)·F{\left(t_i\right)}^k·{\left[1-F\left(t_i\right)\right]}^{n-k}=0.5$$

(4)

With the help of a special dedicated computing program, such as Reliasoft Weibul++, MR can be determined (so we can estimate F(t_i) for any order number i, from 1 to n).

In the absence of a calculation program that allows the calculation of the Median Ranks estimator, it can be approximated by various algebraic estimators, whose calculation is simple in Microsoft Excel, as follows:

-

the Herd–Johnson estimator, used in 1960–1964, but also proposed by Weibull in 1939 [4]:

$$F_i=\frac{i}{n+1}$$

(5)

and estimators [19,20]:

-

the Hazen estimator (2014):

$$F_i=\frac{i-0.5}{n}$$

(6)

-

the Benard estimator (1953):

$$F_i=\frac{i-0.3}{n+0.4}$$

(7)

-

the Blom estimator (1958) [20]:

$$F_i=\frac{i-3/8}{n+1/4}=\frac{i-0.375}{n+0.25}$$

(8)

These estimators of the F(t) indicator led to values that are quite close to the Median Ranks estimator [21], as opposed to the so-called “naive estimator”, defined by the simple relation:

$$F_i=\frac{i}{n}$$

(9)

Although the “naive estimator” expresses exactly the cumulative frequency of failures for the monitored batch of products, in order to obtain a mathematical model that describes as adequately as possible the reliability of the entire population (reliability modeling), the Median Ranks indicator is the most suitable. It represents the F(t_i) value that ensures 50% confidence level, and this essentially means that this is the best estimate for the unreliability.

But the Excel program now contains the BETA.INV function, which for a particular case is identified with the MR estimator, namely for the case BETA.INV (0.5, i, n + 1 − i), when the specified probability is 0.5 (as in MR), and the other two parameters of the function are i and (n + 1 − i).

Thus, in the Excel program the BETA.INV function is the inverse of the cumulative BETA.DIST function [21], defined by the analytic relation [22]:

$$\mathsf{\beta }\left(a,b\right)={{\displaystyle \int }}_0^1{t}^{a-1}{(1-t)}^{b-1}dt, a,b>0$$

(10)

The Microsoft Excel calculator determines MR_i values by calculating the values of the BETA.INV function (0.5, i, n + 1 − i), which is the inverse of the BETA.INV function for the cumulative density of the probability of failure [23]. The way the program works is iterative, calculating with a minimum degree of confidence the value of the variable corresponding to the specified probability.

For a comparative analysis of the presented estimators, the values that re calculated in the case of a complete test of a batch of n = 10 products are presented, at which the failure times t_i [hours] were recorded (

Table 1. Distribution function estimator values F(t).

i	ti [Hours]	i/n	i/(n + 1)	(i − 0.5)/n	(i − 0.3)/(n + 0.4)	(i − 0.375)/(n + 0.25)	BETA.INV (0.5; i; n + 1 − i)
1	210	0.100	0.091	0.050	0.067	0.061	0.067
2	215	0.200	0.182	0.150	0.163	0.159	0.162
3	224	0.300	0.273	0.250	0.260	0.256	0.259
4	228	0.400	0.364	0.350	0.356	0.354	0.355
5	234	0.500	0.455	0.450	0.452	0.451	0.452
6	260	0.600	0.545	0.550	0.548	0.549	0.548
7	267	0.700	0.636	0.650	0.644	0.646	0.645
8	285	0.800	0.727	0.750	0.740	0.744	0.741
9	297	0.900	0.818	0.850	0.837	0.841	0.838
10	300	1.000	0.909	0.950	0.933	0.939	0.933

) and the graphs for F(t) that were obtained with the respective estimators (Figure 2).

Figure 2 shows that the “naive estimator” leads to clearly different results from all the other estimators, instead, all the other estimators are almost identical to the BETA.INV estimator. However, of these, the Benard estimator is most used to estimate the F(t) distribution function when the BETA.INV function is not available.

2.3. Determining the Parameters of the Three-Parametric Weibull Model

For this, the facility offered by the trendline function of Microsoft Excel will be used after the linearization of the Weibull distribution function by the method that is known and used almost invariably in Weibull modeling [24,25]: apply two successive logarithms of the reliability function $R\left(t\right)$ given by the relation (1):

After the first logarithm results:

$$ln\frac{1}{R\left(t\right)}={\left(\frac{t-\gamma }{\eta }\right)}^{\beta }$$

(11)

and, after the second logarithm, the final shape is obtained:

$$lnln\frac{1}{R\left(t\right)}=lnln\frac{1}{1-F\left(t\right)}=\beta ln(t-\gamma )-\beta ln\eta$$

(12)

which represents a linear equation of form:

$$Y=\beta X+c$$

(13)

where:

$$Y=lnln\frac{1}{1-F\left(t\right)},X=ln(t-\gamma ) c=-\beta ln\eta$$

(14)

It follows that in the probabilistic network having on the ordinate the Y scale and on the abscissa the X scale, a distribution of statistical data that are defined by the Weibull law of parameters γ, η, and β will be aligned after a line, also called “Weibull line”, having the inclination that is equal to the value of the parameter β.

The method to be proposed has as a starting point the observation that, under the conditions in which the parameter γ is initially determined, the analytical expression for the Weibull line is reduced to linear relation (13), which contains the shape parameter β and the scale parameter η. This means that the values of the parameters β and η can be determined by linear regression of point distribution Y_i(X_i) using the “trendline” function that is available in current Microsoft Excel spreadsheets [26,27,28].

Figure 3 shows the flowchart for the proposed methodology, which could be easily translated into a calculation program.

Thus, using the “trendline” function, the “linear” option is chosen, and the regression line of the point pair distribution (X_i, Y_i) is constructed, for which the analytical relation and the value of the square of the degree of correlation are obtained R².

According to relation (9), the coefficient of the variable X is the very parameter of the form β, and the free term contains the parameters β and η:

$$c=-\beta ln\eta$$

(15)

From this last relation, knowing the value of the parameter β the parameter η can be obtained analytically, according to the relation:

$$\eta =e^{-\frac{c}{\beta }}$$

(16)

According to the proposed methodology, for the data t_i (I = 1…10) that constitute the case study, the F(t_i) estimator was determined using the BETA.INV function, and then, the quantities ln(t_i) and ln [1/(1 − F)], presented in

Table 2. Calculated values for estimating and linear regression of the distribution function F(t) for γ = 0.

i	ti [Hours]	ln(ti)	F = BETA.INV (0.5; i; n + 1 − i)	lnln [1/(1 − F)]
1	210	5.3471	0.0670	−2.6691
2	215	5.3706	0.1623	−1.7313
3	224	5.4116	0.2586	−1.2067
4	228	5.4293	0.3551	−0.8240
5	234	5.4553	0.4517	−0.5093
6	260	5.5607	0.5483	−0.2297
7	267	5.5872	0.6449	0.0347
8	285	5.6525	0.7414	0.3020
9	297	5.6937	0.8377	0.5980
10	300	5.7038	0.9330	0.9946

with which the graph that is presented in Figure 3 was built.

Thus, for the data distribution that constitutes the case study, for the bi-parametric model (parameter γ = 0), the linear regression in the following figure shows the values of the two parameters: β = 7.831 and $\eta =e^{-\frac{c}{\beta }}=e^{-\frac{-43.461}{7.831}}=267.2475 \left(\mathrm{hours}\right)$ and a coefficient of determination R² = 0.8932.

It is found that the parameter β has a value above 6—too high, as specified in the paper [2], so that the model does not have a proper probability (this is due to the fact that the first failure time is very long, relative to the amplitude distribution, which corresponds to a coefficient of variation ν= σ/m very small), which is confirmed both by the graphic image and by the low value of coefficient of determination: R² = 0.8932. It is obvious that in this case, the bi-parametric Weibull model is inappropriate so it is necessary to adopt a three-parametric model, so a model in which to use the location parameter [9]. It will certainly provide a better likelihood degree. For the estimation of the third parameter (location), the existence of the parameter estimate is an important aspect. This is demonstrated in the paper [20]. There are various methods to identify this parameter, but are more laborious, including the one that is presented in the paper [11]. But very good results are also obtained by adopting for this parameter a value that is close to the first failure time, but strictly lower, because otherwise it will appear as the first value t − γ = 0, for which the logarithmic function is not defined.

Thus, successively using the values for γ that are smaller than t₁ = 210 h, the respective graphs were constructed in the same way as in Figure 4 and thus the respective values for the coefficient of determination were identified, identifying the highest value: for γ = 206 h, coefficient of determination is R² = 0.9740, which is clearly superior to that which was obtained for the bi-parametric Weibull model (when γ = 0). Figure 5 shows the graph R²(γ) made for values γ tested starting with the one immediately below the first failure time, γ = 209 h, noting that the graph shows only one maximum, for γ = 206 h, after which, as the values γ decreases, follows a downward trend that touches for γ = 0 the value that is obtained for the case of the bi-parametric model (R² = 0.8932).

According to the results that are presented in the literature by the correlation coefficient method, but were obtained by much more laborious calculations, including special calculation programs that were designed in Matlab [10], it is recommended to adopt for the location parameter a value that is close to the time of failure (strictly shorter than the first failure time), but the graph shows that this value does not have to be in the immediate vicinity of the moment t₁. The optimum value can be obtained by successive tests (by probing), for values that are smaller and smaller in relation to the first failure time, until the values that are obtained for R² begin to decrease, which confirms that the optimal value has been identified, which is unique.

Thus, after only five attempts with a step of 1 h, it is found, performing the linear regression for each step, that the optimal model was obtained on the fourth attempt (case γ = 206 h), for this optimal three-parametric model (those for which the coefficient of determination is maximum: R² = 0.9740) resulting, after processing the data from the case study (

Table 3. Calculated values for estimating and linear regression of the distribution function F(t) for γ_optimum = 206 h.

i	t [Hours]	Int	F = BETA.INV (0.5; i; n + 1 − i)	lnln [1/(1 − F)]
1	4	1.3863	0.0670	−2.6691
2	9	2.1972	0.1623	−1.7313
3	18	2.8904	0.2586	−1.2067
4	22	3.0910	0.3551	−0.8240
5	28	3.3322	0.4517	−0.5093
6	54	3.9890	0.5483	−0.2297
7	61	4.1109	0.6449	0.0347
8	79	4.3694	0.7414	0.3020
9	91	4.5109	0.8377	0.5980
10	94	4.5433	0.9330	0.9946

) and performing the linear regression (Figure 6), in the values for the other two parameters:

-

shape parameter: β = 1.0382.

-

the scale parameter: $\eta =e^{-\frac{c}{\beta }}=e^{-\frac{-4.0975}{1.0382}}=51.766 \mathrm{h}$.

Thus, it is found that, after determining the optimal value of the location parameter, by simply changing the variable, from t to (t − γ _optimum), it is possible to operate in Microsoft Excel with the function corresponding to the bi-parametric Weibull model, and the operator is, in fact, even the optimal three-parametric model that is defined by the relation:

$$R\left(t\right)=e^{-{\left(\frac{t-206}{51.7}\right)}^{1.0382}}$$

(17)

The fact that the Weibull three-parametric model with γ = 206 h is more suitable is also observed in the graphs below (Figure 7 and Figure 8), where the graphs for the distribution functions F(t) that were obtained on the two paths are presented: statistic (with BETA.INV function) and by bi-parametric Weibull modeling, for the argument t, respectively, for the argument (t-206), the last case being the Weibull three-parametric F_W model for the parameter γ = 206.

The data were obtained with the WEIBULL.DIST function (t; beta; eta; TRUE) from the Microsoft Excel calculation program (

Table 4. Values that were calculated for the distribution function F(t), for t, and (t – γ_optimum).

T [Hours]	BETA.INV	F(t)
		WEIBULL.DIST (t; beta; eta; TRUE)	WEIBULL.DIST (t-206; beta; eta; TRUE)
210	0.0670	0.1404	0.0564
215	0.1623	0.1663	0.1322
224	0.2586	0.2218	0.2623
228	0.3551	0.2503	0.3157
234	0.4517	0.2975	0.3903
260	0.5483	0.5532	0.6392
267	0.6449	0.6292	0.6884
285	0.7414	0.8086	0.7877
297	0.8377	0.8981	0.8365
300	0.9330	0.9155	0.8469

), according to the relation for the distribution function for the parametric Weibull model:

$$F\left(t\right)=1-e^{-{\left(\frac{t}{\eta }\right)}^{\beta }}$$

(18)

With the help of the Excel CORREL function, the correlation coefficient was determined for the values that were obtained with the BETA.INV function and the two models that were obtained: for the bi-parametric Weibull model resulted in the value of 0.9719, while for the three-parametric Weibull model a value significantly higher, of 0.9838.

This supports the efficiency of the proposed method, for which the usual Microsoft Excel program is sufficient to arrive at a three-parameter Weibull model that is more likely than the two-parameter Weibull model.

3. Discussion

The theoretical and applied research that was conducted led to the following findings:

• In many works MR is approximated with various algebraic estimators (i.e., Benard, Hazen etc.), more or less adequate, but now the MR values can be accurately identified, using the BETA.INV function that is available in the Microsoft Excel calculator.
• The use of the location parameter is mandatory in some cases, such as for the product operability, where the location parameter shows the guaranteed operating time of the object.
• By representing in logarithmic coordinates, we determined three-parametric Weibull models for different values that were initially adopted for the location parameter.
• Using as a criterion the coefficient of determination that was obtained using the trendline function for the linear model, it was possible to identify, by successive tests, the optimal value of the location parameter.
• The accuracy with which the optimal parameter γ_optimum is identified is equal to the value of the adopted step t by the user, which can be more or less fine, depending on the desired level of detail.
• The proposed methodology can be easily translated into a calculation program, as can be seen in the flowchart that was made.

4. Conclusions

The estimation of the failure time distribution function, F(t), can be done in the new versions of Microsoft Excel program by directly determining the Median Ranks estimator through the BETA.INV function for the case of the median probability, equal to 0.5.

As a result, using the exact values for Median Ranks, and not estimated by various algebraic estimators (Benard, Hazen, etc.), a more appropriate reliability model will be obtained.

By the well-known graphic-analytical method for estimating the two-parametric Weibull model, the two parameters can be identified—the shape parameter and the scale parameter—but it is possible to identify an appropriate three-parametric model (which also contains the location parameter) by the method that is presented. The optimum value of the location parameter corresponds to the three-parametric Weibull model resulting from the linear regression of the distribution function for which the coefficient of determination value is maximum.

To identify the optimal location parameter, an iteration method was proposed, with a predetermined step and starting with a value that was immediately lower than the first failure time, the results confirming that after a small number of attempts the value of the location parameter is leading to the most likely three-parametric Weibull model.

The applicability of the method can be further improved by transposing it into a calculation program, according to the presented logic scheme.

In this way, a precise and easily applicable method has been created by which an appropriate three-parametric Weibull model can be identified using the current features of Microsoft Excel.