Some questions about Beta.Dist?

[JenniferMurphy]

It looks like the Beta distribution, with the right values for alpha and beta, can have a shape similar to a Normal distribution, but with finite tails.

Unfortunately, the m$ft description of the Beta.Dist function leaves a little to be desired. There are 5 parameters:

• X Required. The value between A and B at which to evaluate the function
• Alpha Required. A parameter of the distribution.
• Beta Required. A parameter of the distribution.
• Cumulative Required. A logical value that determines the form of the function. If cumulative is TRUE, BETA.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
• A Optional. A lower bound to the interval of x.
• B Optional. An upper bound to the interval of x.

I understand all but Alpha and Beta. The explanation, "A parameter of the distribution", is not exactly informative. After a little experimenting, it looks like setting them both to something between 3 and 8 and Cumulative = False yields a curve that looks a lot like a symmetrical Gaussian.

And if I make one of them larger than the other, it skews the curve left or right.

I also learned that the mean = 1/(1+(beta/alpha)).

I have a few questions:

1. Can someone explain the two parameters, Alpha & Beta? In particular, what values most closely approximate a Normal (Guassian) distribution (symmetrical and skewed)?

2. The Beta.Dist function has two forms: Cumulative=True, which is a cumulative distribution function (CDF) and Cumulative=False, which is a probability distribution function (PDF). It seems pretty that the CDF chart shows the cumulative probabilities. It starts at zero and progresses monotonically to "1". But what the heck is the PDF? It can't be a probability. It goes well past 1 and the sum (G18) is almost 10. In column C, I tried normalizing the Y values by dividing them by the sum (G18). Now they do sum to "1", so are these the probabilities? In column D, I calculated the cumulative values. They do add up to "1", but they do not agree with the CDF values in column P as shown in column E. I would really like to understand what the PDF values are.

3. How can I write some VBA code (=XBeta(alpha, beta, a, b)) that will return X, a random number that has a probability distribution equal to the Beta distribution with those parameters? If I call my XBeta UDF 100 or 1,000 times, the set of numbers returned will have that distribution and that mean. This is my most important question.

I have started a sheet to try and analyze the Beta distribution. It's what I used to generate the charts above. Here's the mini-sheet. It does not appear to include the charts.

Normal-like function.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	B	C	D	E	R/C	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3					3	Alpha	Beta	Cum	A	B	Mean			3	Alpha	Beta	Cum	A	B	Mean
4					4	3	3	FALSE	0	1	0.5000			4	3	3	TRUE	0	1	0.5000
5					5									5
6	Beta.Inv	P(X)	Cum	Δ	6	X	F(X)							6	X	F(X)
7	#NUM!	0.0000	0.0000000	0	7	0.00	0							7	0.00	0
8	0.2466	0.0243	0.0243024	-0.0157	8	0.10	0.2430							8	0.10	0.00856
9	0.3266	0.0768	0.1011101	-0.0432	9	0.20	0.7680							9	0.20	0.05792
10	0.3898	0.1323	0.2334233	-0.0703	10	0.30	1.3230							10	0.30	0.16308
11	0.4463	0.1728	0.4062406	-0.0888	11	0.40	1.7280							11	0.40	0.31744
12	0.5000	0.1875	0.5937594	-0.0938	12	0.50	1.8750							12	0.50	0.50000
13	0.5537	0.1728	0.7665767	-0.0840	13	0.60	1.7280							13	0.60	0.68256
14	0.6102	0.1323	0.8988899	-0.0620	14	0.70	1.3230							14	0.70	0.83692
15	0.6734	0.0768	0.9756976	-0.0336	15	0.80	0.7680							15	0.80	0.94208
16	0.7534	0.0243	1.0000000	-0.0086	16	0.90	0.2430							16	0.90	0.99144
17	#NUM!	0.0000	1.0000000	0	17	1.00	0							17	1.00	1
18	Sum	1.0000			18	Sum	9.9990							18
Beta.Dist

Cell Formulas
Range	Formula
B2:E2,G2:W2	B2	=col()
L4,U4	L4	=1/(1+(Beta/Alpha))
P4	P4	=Alpha
Q4	Q4	=Beta
S4	S4	=A
T4	T4	=B
D7	D7	=ProbX
E7:E17	E7	=Q7-D7
D8:D17	D8	=OFFSET(D8,-1,0)+ProbX
P7:P17	P7	=X
Q7:Q17	Q7	=BETA.DIST(X,Alpha,Beta,CumTrue,A,B)
B7:B17	B7	=BETA.INV(X,Alpha,Beta,A,B)
C7:C17	C7	=H7/SumYFalse
C18,H18	C18	=SUM(C7:C17)
F3:F18,O3:O18	F3	=ROW()
H7:H17	H7	=BETA.DIST(X,Alpha,Beta,CumFalse,A,B)

Named Ranges
Name	Refers To	Cells
A	=Beta.Dist!$J$4	S4, B7:B17, H7:H17, Q7:Q17
Alpha	=Beta.Dist!$G$4	U4, P4, L4, B7:B17, H7:H17, Q7:Q17
B	=Beta.Dist!$K$4	T4, B7:B17, H7:H17, Q7:Q17
Beta	=Beta.Dist!$H$4	U4, Q4, L4, B7:B17, H7:H17, Q7:Q17
CumFalse	=Beta.Dist!$I$4	H7:H17
CumTrue	=Beta.Dist!$R$4	Q7:Q17
ProbX	=Beta.Dist!$C$7:$C$17	C18, D7
SumYFalse	=Beta.Dist!$H$18	C7:C17
X	=Beta.Dist!$G$7:$G$17	H7, P7:Q7, B7

 

[JGordon11]

A PDF is a function that you integrate to get the probability of finding a random variable in the interval that you integrated over.

Setting cumulative to TRUE changes the function to return the integration of the PDF from the minimum of the distribution to x (i.e., the CDF).

Integrating over the entire range of a PDF will result in 1 (i.e., the probability of finding a random variable anywhere in in the distribution is certain).

All PDFs will integrate to 1 if integrated over the entire range of the distribution.

For example, the range of the normal distribution is negative infinity to positive infinity. Integrating it over the entire number line = 1. (In practice, since the normal pdf drops off quickly using a number > say 10 standard deviations from the mean is good enough to simulate infinity).

The Beta distribution range is 0 to one. Evaluating the PDF outside of that range results in an error. Integrating it from 0 to 1 will always return 1

To find the probability of x in a range, for example anywhere from 0.4 to 0.6 will be CDF(0.6) - CDF(0.4) which is the same as the integral of the PDF from 0.4 to 0.6.

The Beta PDF is a function of x between 0 and 1 inclusive and the alpha and beta parameters. It involves exponentiation and the gamma function (which is a function similar to factorial except allows non-integer values and is offset by 1, i.e. gamma( n) = (n-1)!, e.g., gamma(5) = 24 = 4!) . The spreadsheet below shows an example of calculating the Beta PDF the long way and the simpler way using BETA.DIST, an example of GAMMA and FACT, and using a beta distribution to calculate a probability. Looking at your beta.dist(3,3) plot one could estimate the probability of finding x between 0.4 and 0.6 to be about one-third - The area under the curve between 0.4 and 0.6 looks to be about one third of the total area under the curve. Calculated below we can see that the estimate is close to the actual value iof about 36.5%.

Book2
	A	B	C	D	E	F	G
1	Alpha	3		Beta Function the hard way	Beta PDF the easy way
2	Beta	3		1.728	1.728
3	x	0.4
4				n	Gamma	Fact
5				5	24	24
6				5.6	61.55391501	24	<= Excel truncates noninteger
7				6	120	120
8
9				Probability of x between 0.4 and 0.6 if PDF is Beta.Dist(Alpha=3, Beta=3)?	0.36512	or	36.5%
Sheet1

Cell Formulas
Range	Formula
D2	D2	=(x^(Alpha-1)) *((1-x)^(Beta-1))/(GAMMA(Alpha)*GAMMA(Beta)/GAMMA(Alpha+Beta))
E2	E2	=BETA.DIST(x,Alpha,Beta,0)
E5:E7	E5	=GAMMA(D5)
F5:F7	F5	=FACT(D5-1)
E9	E9	=BETA.DIST(0.6,3,3,1) - BETA.DIST(0.4,3,3,1)
G9	G9	=E9

Named Ranges
Name	Refers To	Cells
Alpha	=Sheet1!$B$1	D2:E2
Beta	=Sheet1!$B$2	D2:E2
x	=Sheet1!$B$3	D2:E2

 

[JenniferMurphy]

A PDF is a function that you integrate to get the probability of finding a random variable in the interval that you integrated over.

. . .

To find the probability of x in a range, for example anywhere from 0.4 to 0.6 will be CDF(0.6) - CDF(0.4) which is the same as the integral of the PDF from 0.4 to 0.6.

Thanks for the detailed reply. Very informative. But unless I missed something or there is something I don't understand, I don't see a solution to my primary question. That is, how can I write a VBA function that will return a random variable on [0,1] with a probability in accordance with a Beta specific distribution.

I'd like a function like this:

Function XBeta(pAlpha as double, pBeta as double) as Double
Const a as double = 0
Const b as double = 1 

XBeta = Some code that I don't know how to write that will generate a random variable, [I]X[/I], 
              with a probability that fits the Beta distribution with the passed parameters.

End Function

I may be confusing continuous and discrete functions. Perhaps what I need to do it write a function that accepts a third parameter, Inc. If Inc=0.1, it will then calculate the cumulative probabilities for each X = 0.0, 0.1, 0.2, ... , 1.0.

Using the CDF values for a Beta with Alpha = 7, Beta = 3, these would be:

0.0 = 0
0.1 = 0.000003
0.2 = 0.000314
0.3 = 0.004291
0.4 = 0.025035
0.5 = 0.089844
0.6 = 0.231787
0.7 = 0.462381
0.8 = 0.738198
0.9 = 0.947028
1.0 = 1

Then I generate X, a random number on [0,1) and search that list. If X = 0.53757, it falls between 0.7 and 0.8. I could then return either 0.8, if I don't need more precision, or find a way to prorate it between 0.7 and 0.8. If I need better precision, I can set pInc to 0.05 or 0.01.

Is there a better way than that?

I'm going to try writing such a function and see what happens.

 

[JGordon11]

formula =BETA.INV(RAND(),3,3) will return a random variable with probability associated with the beta PDF 3,3 distribution.

If you create 1000 of these and plot a histogram (bar chart) of the bins (say 0 - 0.05, 0.05 - 0.1 ... 0.95 -1) using countif you'll see it follows the BETA PDF that you plotted in your original post.

Book3
	A	B	C	D
1	0.623952
2	0.679037		0.05	0
3	0.412179		0.1	0
4	0.441978		0.15	1
5	0.68581		0.2	4
6	0.684566		0.25	4
7	0.28831		0.3	9
8	0.314057		0.35	11
9	0.531111		0.4	6
10	0.297384		0.45	10
11	0.436703		0.5	13
12	0.255336		0.55	9
13	0.34647		0.6	12
14	0.679571		0.65	7
15	0.758972		0.7	9
16	0.346941		0.75	11
17	0.303254		0.8	6
18	0.809616		0.85	5
19	0.730871		0.9	3
20	0.264482		0.95	0
21	0.115283		1	0
22	0.27452
23	0.62744
24	0.587699
Sheet1

Cell Formulas
Range	Formula
D2	D2	=COUNTIF($A$1:$A$120,"<" & C2)
D3:D21	D3	=COUNTIF($A$1:$A$120,"<" & C3)-SUM(D$2:D2)
A1:A24	A1	=BETA.INV(RAND(),3,3)

 

[JenniferMurphy]

formula =BETA.INV(RAND(),3,3) will return a random variable with probability associated with the beta PDF 3,3 distribution.

If you create 1000 of these and plot a histogram (bar chart) of the bins (say 0 - 0.05, 0.05 - 0.1 ... 0.95 -1) using countif you'll see it follows the BETA PDF that you plotted in your original post.

Book3
	A	B	C	D
1	0.623952
2	0.679037		0.05	0
3	0.412179		0.1	0
4	0.441978		0.15	1
5	0.68581		0.2	4
6	0.684566		0.25	4
7	0.28831		0.3	9
8	0.314057		0.35	11
9	0.531111		0.4	6
10	0.297384		0.45	10
11	0.436703		0.5	13
12	0.255336		0.55	9
13	0.34647		0.6	12
14	0.679571		0.65	7
15	0.758972		0.7	9
16	0.346941		0.75	11
17	0.303254		0.8	6
18	0.809616		0.85	5
19	0.730871		0.9	3
20	0.264482		0.95	0
21	0.115283		1	0
22	0.27452
23	0.62744
24	0.587699
Sheet1

Cell Formulas
Range	Formula
D2	D2	=COUNTIF($A$1:$A$120,"<" & C2)
D3:D21	D3	=COUNTIF($A$1:$A$120,"<" & C3)-SUM(D$2:D2)
A1:A24	A1	=BETA.INV(RAND(),3,3)

Wow!!! That's perfect. I did take a look at Beta.Inv, but I didn't understand it. I was going to try it, but got distracted with other things.

I now have a working UDF. I'll post it here with some sample data as soon as I run a few more tests to make sure I have it right.

Thanks, again, for saving me tons of frustration. ?