Finding critical value using t-distribution in Excel

Question

Alright, I'm trying to figure out how to calculate a critical value using t-distribution in Microsoft Excel... ex. a one-tailed area of 0.05 with 39 degrees of freedom: t=1.685

I know the answer, but how do I get this? I've tried TDIST() TINV() and TTEST() but they all give me different answers. This web calculator: http://www.danielsoper.com/statcalc/calc10.aspx always gives me what I'm looking for but I cannot manage to get Excel to do the same.

Any help would be greatly appreciated!

Answer 1

Non-answer: Export the data to CSV and use software that is suited for statistical work, e.g. R.

Answer 2

The right function is TINV(), but the problem is that TINV() assumes that you're inputting the two-tailed probability, not the one-tailed probability. In other words, TINV(p,d) outputs the value of $x$ such that $P(|X| \geq x) = p$, where $X$ has a $t$ distribution with $d$ degrees of freedom.

Thus if you want TINV() to give you the critical value associated with a one-tailed probability, you have to double the probability first to account for the other tail. Thus, for example, TINV(0.1,39) yields an output of 1.684875122, which is the critical value you want.