T.INV Function in Excel

The T.INV function in Excel gives you the t value that has a certain probability to its left on the t-distribution. It’s the cutoff you need for a confidence interval or a one-tailed t-test when you’re working with a small sample.

You give it a probability and the degrees of freedom, and it returns the t value that has exactly that much of the curve on its left.

The catch is that it always reads the probability from the left, so a probability below 50% gives you a negative number.

I’ll start with the basics, and then use T.INV for a confidence interval and for left-tailed and right-tailed tests.

T.INV Function Syntax

Here is the syntax of the T.INV function:

=T.INV(probability, deg_freedom)

• probability – The probability to the left of the t value you want. For example, 0.05 gives you the t value that has the lowest 5% of the distribution below it.
• deg_freedom – The degrees of freedom of the t-distribution. For a single sample, this is the sample size minus 1.

Both arguments are required.

The probability has to be more than 0 and less than 1. If you give it 0, 1, or a negative number, you get a #NUM! error, and if you give it text that isn’t a number, you get a #VALUE! error.

A number stored as text, like “0.05”, still works because Excel converts it.

The degrees of freedom have to be 1 or more, otherwise you get a #NUM! error. If you enter a decimal like 10.9, Excel cuts off the decimal part and uses 10.

The T.INV function works in Excel 2010 and later versions, including 365, 2024, 2021, 2019, 2016, and Mac.

When to Use the T.INV Function

Use this function when you need to:

• Find the t value that cuts off a given probability on the left side of the curve
• Get the t critical value for a one-tailed t-test
• Calculate a confidence interval for an average from a small sample
• Turn a significance level into the cutoff your t statistic has to beat
• Build your own t-table for any probability and sample size

Let me show you a few practical examples of how to use the T.INV function in Excel.

Example 1: Find the t Value for a Probability

Let’s start with a simple example.

Below I have a list of probabilities, and I want the t value for each one in column B, using 9 degrees of freedom (a sample of 10).

Here is the formula:

=T.INV(A2:A11,9)

Since I’ve given the function the whole range in A2:A11, it returns a t value for every probability, and the results spill down into B2:B11 from this one formula.

With a probability of 0.025, the t value is -2.262, because that’s the point with 2.5% of the curve to its left. With 0.5, you get 0, the center of the curve.

With 0.975, you get 2.262, the exact mirror image. The t-distribution is symmetrical, so this is how you get the upper cutoff of an interval, which I’ll use in Example 3.

Important: T.INV always measures the probability from the left. For the cutoff of the top 5% of the curve, enter 0.95, not 0.05, or you’ll get -1.833 instead of 1.833.

If you are using Excel 2019 or earlier, enter the below formula in cell B2 and then copy it down the range:

=T.INV(A2,9)

Example 2: See How the t Value Changes with Sample Size

Now let’s see what happens to the t value as the sample gets bigger.

Below I have a list of degrees of freedom, and I want the t value that cuts off the lowest 5% and the lowest 1% in columns B and C.

Here is the formula:

=T.INV(B2:C2,A3:A12)

How this formula works:

• B2:C2 has the two probabilities, and it runs across the sheet.
• A3:A12 has the ten degrees of freedom, and it runs down the sheet.
• Since one range runs across and the other runs down, the function returns a value for every pair, and the results spill into the whole B3:C12 grid.

With just 1 degree of freedom (a sample of 2), the 5% cutoff is -6.314. By 10 degrees of freedom it’s -1.812, and by 1000 it’s -1.646.

That last value is almost the same as the -1.645 that the NORM.S.INV function gives for the same 5% probability on the normal curve.

With so little data, the t-distribution plays it safe and uses a much wider cutoff, and the gap closes as the sample grows.

If you are using Excel 2019 or earlier, enter the below formula in cell B3 and then copy it across to C3 and down to row 12:

=T.INV(B$2,$A3)

Example 3: Calculate a Confidence Interval with T.INV

Now let’s use T.INV for something practical.

Below I have the monthly electricity bills of 10 households, and I want the lower and upper limits of a 95% confidence interval for the average bill in E9:E10.

The confidence level is in E2, and the sample size, average, and standard deviation in E3:E5 come from the COUNT, AVERAGE, and STDEV.S functions on the bills in B2:B11.

Here is the formula for the t critical value, which is the cutoff the interval is built on:

=T.INV(1-(1-E2)/2,E3-1)

How this formula works:

• A 95% confidence level leaves 5% outside the interval, split evenly between the two tails. So (1-E2)/2 is the 2.5% in each tail.
• 1-(1-E2)/2 turns that into 97.5%, the probability to the left of the upper cutoff. T.INV only reads from the left, so it needs 0.975 and not 0.025.
• E3-1 gives 9 degrees of freedom (10 households minus 1).

The formula returns 2.262, the same as the 0.975 row in Example 1.

Now here is the formula for the margin of error:

=E7*E5/SQRT(E3)

E5/SQRT(E3) divides the standard deviation by the square root of the sample size. This gives the standard error, which tells you how much the average of 10 households can bounce around.

Multiplying it by the critical value in E7 gives a margin of error of $12.96.

To get the interval, subtract the margin of error from the average for the lower limit. Here is the formula:

=E4-E8

And here is the formula for the upper limit, which adds it instead:

=E4+E8

That puts the 95% confidence interval for the average monthly bill between $99.34 and $125.26. In other words, you can be 95% confident that the true average bill of all households falls in that range.

Since the confidence level sits in E2, you can change it to 90% or 99% and everything updates on its own.

Pro Tip: For a two-sided interval like this one, the T.INV.2T function skips the 1-(1-E2)/2 step, so =T.INV.2T(1-E2,E3-1) returns the same 2.262. The CONFIDENCE.T function gives the margin of error in one step with =CONFIDENCE.T(1-E2,E5,E3).

Example 4: Run a Left-Tailed t-Test with T.INV

Let’s step it up and use T.INV to make a decision.

Below I have the delivery times of 12 orders on a pizza shop’s new route, and I want to check in E8:E10 whether the new route is really faster than the old average of 35 minutes.

The average of these 12 deliveries is 31 minutes, but a handful of deliveries can look faster just by luck. The test checks whether a gap this big could easily happen by luck.

First, here is the formula for the t statistic:

=(E4-E2)/(E5/SQRT(E3))

This measures how far the new average (31) is from the old one (35), in units of standard error. It returns -3.044, which is negative because the new route is faster.

Here is the formula for the critical value:

=T.INV(E6,E3-1)

E6 holds the 5% significance level, which is the risk of being fooled by luck that I’m willing to accept.

We only care whether the new route is faster, which means lower. So the whole 5% goes into the left tail, and T.INV can use it as is.

With 11 degrees of freedom (12 deliveries minus 1), the critical value is -1.796.

And here is the formula that makes the decision:

=IF(E8<E9,"New route is faster","No clear improvement")

The IF function checks whether the t statistic of -3.044 is lower than the critical value of -1.796. It is, so the formula returns “New route is faster”.

In other words, at the 5% significance level there’s enough evidence that the new route is really faster.

Important: In a left-tailed test, the critical value is negative, so the t statistic has to fall below it. If you flip the comparison to greater than, a faster route gets reported as no improvement.

Example 5: Run a Right-Tailed t-Test with T.INV

Now let’s flip it around and test for an increase.

Below I have the change in the average daily steps of 10 employees after a 30-day walking challenge, and I want to check in E7:E9 whether the challenge really raised their steps.

Here is the formula for the t statistic:

=E3/(E4/SQRT(E2))

The changes are already measured against zero, so there’s no target to subtract. The average change of 795 steps divided by the standard error gives a t statistic of 3.799.

Here is the formula for the critical value:

=T.INV(1-E5,E2-1)

This time we’re checking for an increase, so the 5% goes in the right tail. T.INV reads from the left, so 1-E5 turns the 5% into 95%.

With 9 degrees of freedom (10 employees minus 1), the critical value is 1.833.

And here is the formula that makes the decision:

=IF(E7>E8,"Steps increased","No clear increase")

Since 3.799 is above 1.833, the formula returns “Steps increased”. Two employees walked less after the challenge, but a rise this large across the group would be unlikely by luck alone at the 5% level.

So these are some of the ways you can use the T.INV function in Excel. Just remember that it reads the probability from the left, and use 1 minus your probability whenever you need the cutoff on the right side.

The older TINV function looks similar, but it gives the two-sided cutoff instead of the left-tail one. For example, =TINV(0.05,9) returns 2.262, while =T.INV(0.05,9) returns -1.833.

If you want a p-value instead of a cutoff, check out how to calculate the p-value in Excel.

I hope you found this article helpful.

List of All Excel Functions

Other Excel Articles You May Also Like:

• NORM.INV Function in Excel
• CHISQ.INV in Excel
• F.INV guide
• FISHERINV Function in Excel
• T.DIST Function in Excel
• T.DIST.RT Function in Excel
• T.DIST.2T Function in Excel
• T.TEST Function in Excel