Syntax. BETA.INV(probability,alpha,beta,A,B)
Definition. This function returns the inverse of a beta distribution. If probability = BETADIST(x,...), then BETAINV(probability,...) = x. BETA.INV() is the inverse function of BETA.DIST().
The beta distribution can be used in project planning to model completion times based on the expected completion time and variance. The inverse of the function returns the value x (completion time) for a given probability of the beta cumulative frequency distribution.
Arguments
probability (required). A probability associated with the beta distribution
alpha (required). A parameter of the distribution (describes the shape)
beta (required). A parameter of the distribution (describes the shape)
A (optional). The lower limit of the interval for x
B (optional). The upper limit of the interval for x
The alpha and beta arguments describe the shape of the beta distribution. The lower and upper limits transcribe the values for 0 and 1. If A is supplied, then B must also be supplied.
Note
If one of the arguments isn’t a numeric value, the BETA.INV() function returns the #VALUE! error.
If alpha or beta is less than or equal to 0, the BETA.INV() function returns the #NUM! error.
If probability is less than or equal to 0 or greater than 1, the BETA.INV() function returns the #NUM! error.
If you don’t enter values for A and B, the BETAINV() function uses the standard distribution, A = 0 and B = 1.
If probability has a value, BETA.INV() looks for the value x so that BETA.DIST(x, alpha, beta, A, B) = probability. Therefore, the accuracy of BETA.INV() depends on the accuracy of BETA.DIST(). BETA.INV() uses an iterative search technique. If the search has not converged after 100 iterations, the function returns the #N/A error.
Background. Beta distribution is a continuous probability distribution indicating the probability that a random variable x has a certain value in the interval [0,1].
Beta distribution is defined by the probability density (see Figure 12-12):
![The beta density function in an interval of [0.1] with p and q = 1.](http://imgdetail.ebookreading.net/business/52/9780735665064/9780735665064__microsoft-excel-2010__9780735665064__httpatomoreillycomsourcemspimages1633249.png)
Outside the interval [0,1], it is continued with f(x)=0. The parameters are p and q. To ensure standardization, p,q has to be greater than 0. The prefactor 1/B(p;q) ensures the correct standardization (the normalization to a value range from 0 through 1). The expression
stands for the beta function. Γ(p) is the gamma function.
The beta function (or Euler’s beta function) is a mathematical function of two positive real numbers or two complex numbers x and y, defined by the following formula:
The formula is often denoted with B(x,y). Excel uses the cumulative distribution (see Figure 12-13).
![The cumulative beta distribution function in an interval of [0.1] with p and q = 1.](http://imgdetail.ebookreading.net/business/52/9780735665064/9780735665064__microsoft-excel-2010__9780735665064__httpatomoreillycomsourcemspimages1633255.png)
The following equations demonstrate the expected value and variance of the cumulative beta distribution:
The BETA.INV() function returns the inverse of the beta distribution.
Example. To practice calculating BETA.INV(), use the following example values:
0.685470581 = the probability associated with the beta distribution
8 = parameter of the distribution
10 = parameter of the distribution
1 = lower limit
3 = upper limit
With these parameters, the BETA.INV() function returns the quantile 2 for the given beta distribution.
You have described the shape of the cumulative beta distribution with the distribution parameters 8 and 10. You want to know what value you could expect with a probability of 0.6854. The result would be returned in the range 0 to 1, but because you are using lower and upper limits of 1 and 3 respectively, the answer is converted to this range, resulting in 2 (see Figure 12-14).
