How to use the BETA.INV function
What is the BETA.INV function?
The BETA.INV function calculates the inverse of the cumulative beta distribution. This function has replaced the BETAINV function and was introduced in Excel 2010.
The beta distribution can help estimate how long a project might take by using the expected finish time and how variable the timeline could be. It gives the chance the project will be done at different dates based on the variability.
Table of Contents
1. Introduction
What is the inverse of the cumulative beta distribution?
The inverse of the cumulative beta distribution is a function that returns the value of x for a given probability p and parameters α and β of the beta distribution.
What is a cumulative beta probability distribution?
The cumulative beta distribution function gives the probability that a beta-distributed random variable with parameters α and β will be less than or equal to a given value x, providing the accumulated area under the probability density curve from 0 to x.
What is a beta probability density distribution?
A beta probability density distribution is a function whose shape over [0,1] depends on parameters α and β that gives the relative likelihood of a beta-distributed random variable occurring at different points, whose total area under the curve integrates to 1.
What are continuous values?
Continuous values are numbers that can take on any quantity within a range and can have infinitely many possibilities, unlike discrete values which have distinct separated values; continuous values can use intervals and ranges to describe events rather than fixed outcomes.
What are discrete probabilities?
Discrete probabilities are individual separated probabilities assigned to each of a countable number of possible outcomes that sum to 1, like rolling a die where each number has its own exact probability, as opposed to continuous distributions.
What are binomial success probabilities?
Binomial success probabilities describe the chance of a certain number of “successes” occurring in a fixed number of independent binary trial events modeled by the binomial distribution, like the probability of getting 3 heads in 10 coin flips.
What is Bayesian statistics?
Bayesian statistics is an approach to statistics using Bayes' theorem where prior beliefs about probabilities are updated as new evidence is acquired to determine conditional probabilities and update understanding of likelihood.
2. BETA.INV function Syntax
The BETA.INV function has three required arguments and two optional arguments.
BETA.INV(probability,alpha,beta,[A],[B])
3. BETA.INV function Arguments
| Argument | Description |
| probability | Required. |
| alpha | Required. A distribution parameter. |
| beta | Required. A distribution parameter. |
| [A] | Optional. Lower bound, default value 0 (zero). |
| [B] | Optional. Upper bound, default value 1. |
4. BETA.INV function example 1
The BETA.INV function calculates the inverse of the cumulative beta distribution representing an outcome between 0 and 1.
The BETA.DIST function is related to the BETA.INV function: BETA.INV(probability,alpha,beta,[A],[B]) = x
BETA.DIST(x,alpha,beta,cumulative,[A],[B]) = probability
A company manufactures a new product. The first 10 products has 7 working and 3 faulty. Assuming that the proportion of working products follows a beta distribution with parameters α = 8 and β = 4, find the values of the proportions corresponding to the 2.5th and 97.5th percentiles?
Alpha is the number of working products plus one and beta is the number of faulty products plus one. The ratio between the number of working products and the total number of products is 7/10 equals 0.7 The blue line has it's highest point at 0.7 if you check the x-axis.
The image above has argument
- probability in cell C18 and that value is 0.975
- alpha in cell C19
- beta in cell C20
Formula in cell C10:
The formula returns approx. 0.891 for a probability of 97.5% and approx. 0.390 for a probability of 2.5%.
There is a 97.5% probability that the proportion of working products is less than or equal to 0.891 (or 89.1%), assuming the given beta distribution parameters.
If the proportion of working products follows a beta distribution with parameters α = 8 and β = 4 the value of x so that the probability of observing a proportion less than or equal to x is 2.5% is approximately 0.390 or 39.0%.
5. BETA.INV function example 2
This example continues on example 1 above. The company has now manufactured 100 products, 90 working and 10 defect.
Assuming that the proportion of working products follows a beta distribution with parameters α = 91 and β = 11, find the values of the proportion corresponding to the 2.5th and 97.5th percentiles?
Alpha is the number of working products plus one and beta is the number of faulty products plus one. The ratio between the number of working products and the total number of products is 90/100 equals 0.9 The blue line has it's highest point at 0.9 if you check the x-axis.
The BETA.INV function returns 0.944 for 97.5% and 0.825 for 2.5 %.
This example shows that as the number of observations increases the probability density curve (blue) gets more narrow meaning the uncertainty is also decreasing. In other words, the function gets better as the number of observations increases.
6. BETA.INV function example 3
The optional arguments [A] and [B] are lower and upper limits respectively. The BETAINV function uses these limits to assist you calculating the x value for you which is a number between the lower limit and the upper limit.
You will get the same result if you calculate the x value yourself, here is an example.
The arguments are:
- probability = 0.921957262116145
- alpha = 90
- beta = 110
- [A] = 15
- [B] = 45
Formula in cell C7:
The formula in cell C7 returns 30.You can calculate x using only three arguments probability, alpha, and beta. Multiply the result with the total of the upper and lower limit to calculate x. It will be somewhere between the limits. Here is how:
returns 0.5
0.5*(15+45)=30 which is exactly what the function returns in cell C24
7. BETA.INV function not working
The BETAINV function returns
- #VALUE! error value if any argument is non-numeric.
- #NUM! error value if:
- alpha <= 0 (zero)
- beta <= 0 (zero)
- probability <= 0 (zero)
- probability > 1
- A = B
Useful resources
Functions in 'Statistical' category
The BETA.INV function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form