Excel statistical functions: BETAINV

The purpose of this article is to describe the BETAINV function in Microsoft Office Excel 2003 and in Microsoft Office Excel 2007. This article also discusses an improvement in Excel 2003 and in Excel 2007 that might affect the results of the BETAINV function for Excel 2003 and for Excel 2007 when compared to the results of the BETAINV function in earlier versions of Excel.

Note In this article, p is a probability with 0 < p < 1, alpha and beta are positive numbers, and A and B are numbers with A < B. If A and B are omitted, they are assumed to be 0 and 1, respectively.

The BETAINV(p, alpha, beta, A, B) function is the inverse function for BETADIST(x, alpha, beta, A, B). For any particular x, BETADIST(x, alpha, beta, A, B) returns the probability that a Beta-distributed random variable (with the parameters alpha, beta, A, and B) is less than or equal to x. In other words, BETADIST returns the cumulative probability that is associated with x. If A and B are removed, they are assumed to be 0 and 1, respectively.

The BETAINV(p, alpha, beta, A, B) function returns the value of x where BETADIST(x, alpha, beta, A, B) returns p. Therefore, BETAINV is evaluated by a search process that returns the appropriate value of x by evaluating BETADIST for various candidate values of x until it finds a value of x where BETADIST(x, alpha, beta, A, B) is "acceptably close" to p.

Syntax

Example Usage

To illustrate BETAINV, follow these steps:

1. Create a blank Excel worksheet, copy the following table, select cell A1 in your blank Excel worksheet, and then on the Edit menu, click Paste so that the entries in the table fill cells A1:C18 in your worksheet.
2. After you paste this table into your new Excel worksheet, click Paste Options, and then click Match Destination Formatting.
3. While the pasted range is selected, on the Format menu, point to Column, and then click AutoFit Selection.
   
   Note You might want to format cells B5:C8 so that they are easier to read. For example, you can format Number with 3 decimal places.

The first two columns of the worksheet illustrate the standard Beta distribution with the optional parameters A and B omitted. You can investigate different parameter settings by changing the values in cells B1:B2. The standard Beta distribution has values between 0 and 1. Cells A5:A8 give the cumulative probability of an observed value that is less than or equal to 0.2, 0.4, 0.6, and 0.8. Cells B5:B8 verify the inverse relationship between BETADIST and BETAINV. Depending on the formatting of cells B5:B8, the values that are returned may or may not be exactly equal to 0.2, 0.4, 0.6, and 0.8, respectively. These numbers are considered equal for practical purposes (for example, the numbers agree to at least 5 decimal places).

Cells A10:A18 use BETAINV to show various cutoff points of the standard Beta distribution cumulative. You can investigate the shape of the Beta distribution cumulative for various values of alpha and beta by changing the entries in cells B1:B2.

The non-standard Beta distribution case is illustrated in cells C5:C8 where A = 10 and B = 20. The standard Beta distribution is basically shifted to the right so that its lower endpoint is at 10 instead of at 0. It is also spread out so that probability fills an interval of width 10 (from 10 to 20) instead of width 1 (from 0 to 1). You can see how the results of BETAINV in cells C5:C8 relate to those in cells B5:B8. In cell B5, 0.2 is 0 + 0.2 * 1, where 0 and 1 are the left endpoint and width (respectively) of the range of the standard Beta distribution. In cell C5, 12 is 10 + 0.2 * 10, where 10 and 10 are the left endpoint and width of the range (that is, B – A) of the Beta distribution when A = 10 and B = 20. Similar relationships exist between corresponding entries in cells B6:B8 and cells C6:C8.

Results in Earlier Versions of Excel

BETAINV(p, alpha, beta, A, B) is found through an iterative process that repeatedly evaluates BETADIST(x, alpha, beta, A, B) and returns a value of x such that BETADIST(x, alpha, beta, A, B) is "acceptably close" to p. Therefore, the accuracy of BETAINV depends on the following two factors:

• The accuracy of BETADIST
• The design of the search process and the definition of "acceptably close."

In rare cases, "acceptably close" as defined in earlier versions of Excel might not be sufficiently close. This is unlikely to affect most users. Basically, if you request BETAINV(p, alpha, beta, A, B), the search continues until a value of x is found where BETADIST(x, alpha, beta, A, B) differed from p by less than 0.0000003.

Results in Excel 2003 and in Excel 2007

No changes were made in Excel 2003 and in Excel 2007 to BETADIST. The only change that affects BETAINV is that "acceptably close" is redefined in the search process to be much closer. The search now continues until the closest possible value of x is found (within the limits of finite precision arithmetic in Excel). The resulting x should have a BETADIST(x, alpha, beta, A, B) value that differs from p by about 10^(-15).

Conclusions

Many inverse functions have been improved for Excel 2003 and for Excel 2007. Some have been improved for Excel only by continuing the search process to reach a higher level of refinement. This set of inverse functions includes the following functions:

• BETAINV
• CHIINV
• FINV
• GAMMAINV
• TINV

No modifications were made to the functions that are called by the inverse functions in the previous list:

• BETADIST
• CHIDIST
• FDIST
• GAMMADIST
• TDIST

Additionally, the same improvement in the search process was made for NORMSINV in Excel 2002. In Excel 2003 and in Excel 2007, the accuracy of NORMSDIST (called by NORMSINV) is also improved. These changes also affect NORMINV and LOGINV (which call NORMSINV) and NORMDIST and LOGNORMDIST (which call NORMSDIST).