Anthony's VBA Forum

[Lim]

Any idea of how to translate the t-statistic and f-statistic into p-value using VBA. I have tried the TDist(x,df,tail) and FDist(x,df1,df2) but unable to obtain the result.

[Anthony]

Hi Lim,

Since both Student-t distribution and the F distribution are non-parametric, you would need to simulate the P value (percentile value). For example, you simulate the F distribution random deviates (with sufficient iteration) then you sort the deviates. Assume you want to get the P at 0.95 with degree of freedon = 10. Then here are the steps to follow:

1. simulate F deviates based on df = 10, for example, 10,000 times
2. sort the deviates
3. locate the 9,500th (0.95 x 10,000) sorted deviate. That is the P value for F distribution at df=10.

Anthony
P.S. I am currently developing various random numbers generators. F distribution and t distribution are included. They will be available in the CD in December.

[Lim]

Thanx. Please let me know when your CD is ready.

[Anthony]

Hi Lim,

I just discovered that by using Excel statistic functions, you can get the P value. To get the percentile value or the probability from a F distribution, use FINV() and FDIST(), respectively. I do not know why it did not work for you (unless your definition of P value is different from what I understand).

For example, FINV(0.05,30,30) = 1.84 and FDIST(1.84,30,30)=0.05.
1.84 is the 0.95 (1-0.05) percentile value and the 0.05 is the probabity that the random value will be greater than 1.84.

Hope this help. If you are still interested in generating random number from various probability distribution, there will be new examples added to the CD in December. They include Student-T, F, Chi-Square, Lognormal, Log Pearson III, and our old friend Noraml Distribution.

Take a preview look on this:
http://anthony-vba.kefra.com/vba/vbar1.htm

Anthony

[Ranf1]

Given a log-normal time series of monthly asset returns, it's a simple matter to compute the student t-distribution particularly using Excel's built-in function, but how do you compute this distribution characterized by a Power Law Index? Appreciative of any Excel examples!