Excel functions

How to Use the TTEST Function in Excel

Today, let’s delve into statistical analysis using Excel and Google Sheets by examining the TTEST function. The TTEST function calculates the probability associated with a Student’s t-test, which is designed to assess whether two datasets’ means differ significantly. This function is crucial in hypothesis testing when comparing sample means.

The Syntax

The syntax for the TTEST function varies slightly between Excel and Google Sheets.

Excel:

=TTEST(array1, array2, tails, type)

  • array1: The first dataset in the t-test.
  • array2: The second dataset in the t-test.
  • tails: Indicates the number of tails in the distribution. Use 1 for a one-tailed test and 2 for a two-tailed test.
  • type: Designates the t-test type: 1 for paired, 2 for two-sample with equal variances, and 3 for two-sample with unequal variances. By default, this is set to 1 if unspecified.

Google Sheets:

=TTEST(range1, range2, tails, type)

  • range1: The first data range for the t-test.
  • range2: The second data range for the t-test.
  • tails: Identical to Excel’s definition, specifies whether the test is one-tailed or two-tailed.
  • type: Identical to Excel’s definition, it denotes the type of t-test.

Examples of Use

Below are specific examples demonstrating how to employ the TTEST function in both Excel and Google Sheets.

Example 1: Two-Sample T-Test

Consider two datasets in Excel located at A1:A10 and B1:B10. We aim to examine if their means differ significantly.

=TTEST(A1:A10, B1:B10, 2, 2)

This formula returns the p-value linked with the two-sample t-test. If this p-value is below the chosen significance level (e.g., 0.05), the null hypothesis that the means are equal is rejected.

Example 2: Paired T-Test

In Google Sheets, consider paired data within columns A and B (A1:A10 and B1:B10) for a paired t-test:

=TTEST(A1:A10, B1:B10, 2, 1)

This sets the type argument to 1 for a paired t-test, outputting the p-value. If this value is sufficiently low, it leads to the rejection of the null hypothesis.

Conclusion

Excel and Google Sheets’ TTEST function is an invaluable asset for conducting t-tests. By mastering its syntax and application scenarios, you can adeptly perform statistical analyses and make well-informed decisions from your data.

More information: https://support.microsoft.com/en-us/office/ttest-function-1696ffc1-4811-40fd-9d13-a0eaad83c7ae

Other functions
BETADIST
Returns the beta cumulative distribution function
BETAINV
Returns the inverse of the cumulative distribution function for a specified beta distribution
BINOMDIST
Returns the individual term binomial distribution probability
CHIDIST
Returns the one-tailed probability of the chi-squared distribution
CHIINV
Returns the inverse of the one-tailed probability of the chi-squared distribution
CHITEST
Returns the test for independence
CONFIDENCE
Returns the confidence interval for a population mean
COVAR
Returns covariance, the average of the products of paired deviations
CRITBINOM
Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
EXPONDIST
Returns the exponential distribution
FDIST
Returns the F probability distribution
FLOOR
Rounds a number down, toward zero
FTEST
Returns the result of an F-test
GAMMADIST
Returns the gamma distribution
GAMMAINV
Returns the inverse of the gamma cumulative distribution
HYPGEOMDIST
Returns the hypergeometric distribution
LOGINV
Returns the inverse of the lognormal cumulative distribution
LOGNORMDIST
Returns the cumulative lognormal distribution
MODE
Returns the most common value in a data set
NEGBINOMDIST
Returns the negative binomial distribution
NORM.INV
Returns the inverse of the normal cumulative distribution
NORMDIST
Returns the normal cumulative distribution
NORMSDIST
Returns the standard normal cumulative distribution
NORMSINV
Returns the inverse of the standard normal cumulative distribution
PERCENTILE
Returns the k-th percentile of values in a range
PERCENTRANK
Returns the percentage rank of a value in a data set
POISSON
Returns the Poisson distribution
QUARTILE
Returns the quartile of a data set
RANK
Returns the rank of a number in a list of numbers
STDEV
Estimates standard deviation based on a sample
STDEVP
Calculates standard deviation based on the entire population
TDIST
Returns the Student's t-distribution
TINV
Returns the inverse of the Student's t-distribution
VAR
Estimates variance based on a sample
VARP
Calculates variance based on the entire population
WEIBULL
Calculates variance based on the entire population, including numbers, text, and logical values
ZTEST
Returns the one-tailed probability-value of a z-test