T.INV Function in Google Sheets: Complete Guide

Key Insights

• T.INV returns the left-tailed inverse of the Student’s t-distribution, essential for calculating confidence intervals and critical values when working with small samples or unknown population standard deviations.
• The function takes two parameters—probability and degrees of freedom—and is distinct from T.INV.2T, which handles two-tailed distributions commonly used in hypothesis testing.
• Most confidence interval calculations require T.INV.2T rather than T.INV, a common source of confusion that leads to incorrect statistical conclusions.

Introduction to T.INV

The T.INV function in Google Sheets returns the left-tailed inverse of the Student’s t-distribution. In practical terms, it answers the question: “What t-value corresponds to a given cumulative probability?”

This matters because the t-distribution is fundamental to statistical inference when you’re working with sample data and don’t know the population standard deviation—which is almost always the case in real-world analysis. Whether you’re calculating confidence intervals for customer satisfaction scores, testing whether a new feature improved conversion rates, or comparing performance metrics between two groups, the t-distribution provides the mathematical foundation.

T.INV specifically handles one-tailed scenarios. If you’re testing whether a value is significantly less than some threshold (a left-tailed test), T.INV gives you the critical value you need. For two-tailed tests—the more common scenario where you’re testing for any significant difference—you’ll use its sibling function, T.INV.2T.

Understanding when and how to use T.INV separates spreadsheet users who copy formulas from those who actually understand what their statistical analysis is doing.

Syntax and Parameters

The T.INV function follows a straightforward syntax:

T.INV(probability, degrees_of_freedom)

probability: The cumulative probability associated with the t-distribution. This value must be greater than 0 and less than 1. Think of it as the area under the curve to the left of the t-value you’re seeking.

degrees_of_freedom: A positive integer representing the degrees of freedom for the t-distribution. For a single sample, this is typically n - 1 where n is your sample size. This parameter must be at least 1.

Here’s a basic example:

=T.INV(0.05, 10)

This returns approximately -1.812, which is the t-value where 5% of the distribution lies to its left with 10 degrees of freedom. The negative value makes sense—we’re looking at the left tail.

For a more intuitive example, finding the median of a t-distribution:

=T.INV(0.5, 10)

This returns 0 because the t-distribution is symmetric around zero, and exactly 50% of values fall below the mean.

Common parameter errors include:

• Probability of 0 or 1: Returns #NUM! error
• Probability outside 0-1 range: Returns #NUM! error
• Degrees of freedom less than 1: Returns #NUM! error
• Non-numeric inputs: Returns #VALUE! error

T.INV vs T.INV.2T

This distinction trips up even experienced analysts. Both functions work with the t-distribution, but they answer different questions.

T.INV returns the t-value for a specified left-tail probability. Use it for one-tailed tests where you’re specifically testing if something is less than a threshold.

T.INV.2T returns the t-value for a two-tailed probability, split equally between both tails. Use it for two-tailed tests (testing for any difference) and for confidence interval calculations.

Here’s a side-by-side comparison:

=T.INV(0.05, 20)      // Returns: -1.725
=T.INV.2T(0.05, 20)   // Returns: 2.086

Notice the dramatic difference. With the same inputs:

• T.INV(0.05, 20) gives the t-value where 5% of the distribution is to the left
• T.INV.2T(0.05, 20) gives the t-value where 5% is split between both tails (2.5% in each)

For a 95% confidence interval, you want T.INV.2T(0.05, df), not T.INV(0.05, df). This is the most common mistake I see in spreadsheet-based statistical analysis.

To get the same result from T.INV as T.INV.2T, you’d use:

=ABS(T.INV(0.025, 20))   // Returns: 2.086 (same as T.INV.2T(0.05, 20))

The probability is halved because we’re only looking at one tail.

Calculating Confidence Intervals

Confidence intervals are the bread and butter of statistical analysis, and T.INV.2T (not T.INV) is your tool for calculating them when working with sample data.

Let’s build a complete confidence interval calculation. Assume you have response times from 15 API calls in cells A2:A16 and want a 95% confidence interval for the true mean response time.

// Sample mean
=AVERAGE(A2:A16)

// Sample standard deviation
=STDEV(A2:A16)

// Sample size
=COUNT(A2:A16)

// Standard error
=STDEV(A2:A16)/SQRT(COUNT(A2:A16))

// Critical t-value for 95% confidence
=T.INV.2T(0.05, COUNT(A2:A16)-1)

// Margin of error
=T.INV.2T(0.05, COUNT(A2:A16)-1) * STDEV(A2:A16)/SQRT(COUNT(A2:A16))

// Lower bound
=AVERAGE(A2:A16) - T.INV.2T(0.05, COUNT(A2:A16)-1) * STDEV(A2:A16)/SQRT(COUNT(A2:A16))

// Upper bound
=AVERAGE(A2:A16) + T.INV.2T(0.05, COUNT(A2:A16)-1) * STDEV(A2:A16)/SQRT(COUNT(A2:A16))

Here’s a consolidated formula that returns the margin of error directly:

=T.INV.2T(0.05, COUNT(A2:A16)-1) * STDEV(A2:A16) / SQRT(COUNT(A2:A16))

For different confidence levels, adjust the first parameter:

• 90% confidence: T.INV.2T(0.10, df)
• 95% confidence: T.INV.2T(0.05, df)
• 99% confidence: T.INV.2T(0.01, df)

When would you use T.INV instead? Only for one-sided confidence intervals, which are rare. For example, if you only care about the upper bound of defect rates:

// Upper one-sided 95% confidence bound
=AVERAGE(A2:A16) - T.INV(0.05, COUNT(A2:A16)-1) * STDEV(A2:A16)/SQRT(COUNT(A2:A16))

Hypothesis Testing Applications

T.INV shines in hypothesis testing when you need critical values for decision-making. Here’s how to set up a complete t-test framework.

For a one-sample t-test comparing your sample mean to a hypothesized value:

// Given: Sample data in A2:A16, hypothesized mean in B1, alpha in B2

// Calculate test statistic
=(AVERAGE(A2:A16) - B1) / (STDEV(A2:A16) / SQRT(COUNT(A2:A16)))

// Critical value for left-tailed test
=T.INV(B2, COUNT(A2:A16)-1)

// Critical value for right-tailed test
=T.INV(1-B2, COUNT(A2:A16)-1)

// Critical value for two-tailed test
=T.INV.2T(B2, COUNT(A2:A16)-1)

Building a decision framework:

// For a two-tailed test at alpha = 0.05
=IF(ABS((AVERAGE(A2:A16)-B1)/(STDEV(A2:A16)/SQRT(COUNT(A2:A16)))) > T.INV.2T(0.05, COUNT(A2:A16)-1), 
    "Reject null hypothesis", 
    "Fail to reject null hypothesis")

For a two-sample t-test comparing two groups (assuming equal variances):

// Group 1 in A2:A16, Group 2 in B2:B20
// Pooled degrees of freedom
=COUNT(A2:A16) + COUNT(B2:B20) - 2

// Critical t-value for two-tailed test
=T.INV.2T(0.05, COUNT(A2:A16) + COUNT(B2:B20) - 2)

Common Errors and Troubleshooting

#NUM! Error: This occurs when parameters are out of valid ranges.

=T.INV(0, 10)      // Error: probability cannot be 0
=T.INV(1, 10)      // Error: probability cannot be 1
=T.INV(0.05, 0)    // Error: degrees of freedom must be >= 1
=T.INV(1.5, 10)    // Error: probability must be < 1

#VALUE! Error: Non-numeric inputs cause this.

=T.INV("five", 10)   // Error: text instead of number
=T.INV(0.05, "ten")  // Error: text instead of number

Confusing T.INV with NORM.INV: NORM.INV uses the normal distribution, appropriate only for large samples (n > 30) or when population standard deviation is known. For small samples with unknown population parameters, always use T.INV.

=NORM.INV(0.025, 0, 1)  // Returns: -1.96 (z-value)
=T.INV(0.025, 10)       // Returns: -2.23 (t-value with df=10)

The t-distribution has heavier tails, producing larger critical values that account for additional uncertainty in small samples.

Using T.INV when T.INV.2T is needed: For two-tailed tests and confidence intervals, always use T.INV.2T. Using T.INV with the full alpha value will give you incorrect critical values.

Practical Tips and Alternatives

Dynamic degrees of freedom: Never hardcode degrees of freedom. Use COUNT to make formulas adapt to data changes:

=T.INV.2T(0.05, COUNT(A:A)-1)

For named ranges, this becomes even cleaner:

=T.INV.2T(0.05, COUNT(SampleData)-1)

Combining with CONFIDENCE.T: Google Sheets provides CONFIDENCE.T for direct margin of error calculation:

=CONFIDENCE.T(0.05, STDEV(A2:A16), COUNT(A2:A16))

This is equivalent to:

=T.INV.2T(0.05, COUNT(A2:A16)-1) * STDEV(A2:A16) / SQRT(COUNT(A2:A16))

Apps Script for automation: For repeated analysis, wrap your calculations in a custom function:

function confidenceInterval(range, alpha) {
  const values = range.flat().filter(v => typeof v === 'number');
  const n = values.length;
  const mean = values.reduce((a, b) => a + b, 0) / n;
  const stdev = Math.sqrt(values.reduce((sum, v) => sum + Math.pow(v - mean, 2), 0) / (n - 1));
  const tCrit = jStat.studentt.inv(1 - alpha/2, n - 1); // Requires jStat library
  const margin = tCrit * stdev / Math.sqrt(n);
  return [[mean - margin, mean, mean + margin]];
}

Quick reference for common critical values:

As degrees of freedom increase, t-values converge to z-values (normal distribution). This is why the distinction matters most for small samples.