Hypothesis test functions

Decide whether a change is real or luck.

Syntax

Write the function's name with its arguments. The tests take lists of numbers and answer a chance from 0 to 1; the distribution functions take a value, and the degrees of freedom the test used.

Z.TEST(array, x, [sigma])
ZTEST(array, x, [sigma])
T.TEST(array1, array2, tails, type)
TTEST(array1, array2, tails, type)
CONFIDENCE.T(alpha, standard_dev, size)
T.DIST.2T(x, degrees_freedom)
T.DIST.RT(x, degrees_freedom)
T.INV(probability, degrees_freedom)
T.INV.2T(probability, degrees_freedom)
CHISQ.DIST.RT(x, degrees_freedom)
CHISQ.INV(probability, degrees_freedom)
CHISQ.INV.RT(probability, degrees_freedom)
FDIST(x, numerator_df, denominator_df)
F.DIST.RT(x, numerator_df, denominator_df)
FINV(probability, numerator_df, denominator_df)
FINV.RT(probability, numerator_df, denominator_df)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • Z.TEST

    array

    Required: Yes
  • x

    Required: Yes
  • sigma

    Required: No
  • ZTEST

    array

    Required: Yes
  • x

    Required: Yes
  • sigma

    Required: No
  • T.TEST

    array1

    Required: Yes
  • array2

    Required: Yes
  • tails

    Required: Yes
  • type

    Required: Yes
  • TTEST

    array1

    Required: Yes
  • array2

    Required: Yes
  • tails

    Required: Yes
  • type

    Required: Yes
  • CONFIDENCE.T

    alpha

    Required: Yes
  • standard_dev

    Required: Yes
  • size

    Required: Yes
  • T.DIST.2T

    x

    Required: Yes
  • degrees_freedom

    Required: Yes
  • T.DIST.RT

    x

    Required: Yes
  • degrees_freedom

    Required: Yes
  • T.INV

    probability

    Required: Yes
  • degrees_freedom

    Required: Yes
  • T.INV.2T

    probability

    Required: Yes
  • degrees_freedom

    Required: Yes
  • CHISQ.DIST.RT

    x

    Required: Yes
  • degrees_freedom

    Required: Yes
  • CHISQ.INV

    probability

    Required: Yes
  • degrees_freedom

    Required: Yes
  • CHISQ.INV.RT

    probability

    Required: Yes
  • degrees_freedom

    Required: Yes
  • FDIST

    x

    Required: Yes
  • numerator_df

    Required: Yes
  • denominator_df

    Required: Yes
  • F.DIST.RT

    x

    Required: Yes
  • numerator_df

    Required: Yes
  • denominator_df

    Required: Yes
  • FINV

    probability

    Required: Yes
  • numerator_df

    Required: Yes
  • denominator_df

    Required: Yes
  • FINV.RT

    probability

    Required: Yes
  • numerator_df

    Required: Yes
  • denominator_df

    Required: Yes

Examples

Eight recent order values tested against a target average of 50: the chance of seeing an average this high if the true average were 50, using the list's own spread and using a known spread.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
Z.TESTdecimalThe chance of an average at least this high if the true average is x, from 0 to 1Z.TEST([52, 48, 61, 55], 50)
ZTESTdecimalThe same as Z.TESTZTEST([52, 48, 61, 55], 50, 6)
T.TESTdecimalThe chance of a difference this large by luck, from 0 to 1T.TEST([48, 52, 45], [53, 58, 47], 2, 1)
TTESTdecimalThe same as T.TESTTTEST([48, 52, 45], [53, 58, 47], 2, 3)
CONFIDENCE.TdecimalThe margin to add and subtract around the average, from the t distributionCONFIDENCE.T(0.05, 12.5, 15)
T.DIST.2TdecimalThe chance of a t value this far from 0 in either directionT.DIST.2T(2.3, 14)
T.DIST.RTdecimalThe chance of a t value this high or higherT.DIST.RT(2.3, 14)
T.INVdecimalThe t value with that chance to its leftT.INV(0.95, 14)
T.INV.2TdecimalThe t value that leaves that chance outside it, in both ends togetherT.INV.2T(0.05, 14)
CHISQ.DIST.RTdecimalThe chance of a chi-square value this high or higherCHISQ.DIST.RT(7.8, 3)
CHISQ.INVdecimalThe chi-square value with that chance to its leftCHISQ.INV(0.95, 3)
CHISQ.INV.RTdecimalThe chi-square value with that chance to its rightCHISQ.INV.RT(0.05, 3)
FDISTdecimalThe chance of an F value this high or higherFDIST(3.1, 4, 20)
F.DIST.RTdecimalThe same as FDISTF.DIST.RT(3.1, 4, 20)
FINVdecimalThe F value with that chance to its rightFINV(0.05, 4, 20)
FINV.RTdecimalThe same as FINV. The spelling F.INV.RT is also acceptedFINV.RT(0.05, 4, 20)
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001An argument is not a number, a list is too short, tails or type is not one of the values allowed, or a degrees of freedom or chance is out of rangePass numbers in range
Limitations
  • T.TEST with type 1 needs two lists of the same length and at least 2 pairs; types 2 and 3 need at least 2 values in each list. The tails must be 1 or 2 and the type 1, 2 or 3.
  • T.INV and T.INV.2T need a chance strictly between 0 and 1. T.DIST.2T needs a t value of 0 or more.
  • FDIST, F.DIST.RT and FINV take whole degrees of freedom of at least 1; FDIST and F.DIST.RT need an F value of 0 or more.
  • CONFIDENCE.T refuses a sample size under 2, and CHISQ.INV.RT a chance outside 0 to 1. Both cut a decimal count to a whole number.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Read a small answer from T.TEST or Z.TEST (under 0.05) as a difference that luck rarely explains, then decide if it is big enough to matter.
  • Use CONFIDENCE.T for a small sample and CONFIDENCE.NORM, on the Distributions page, for a large one.
  • Keep the pairs in the same order in both lists for a paired T.TEST.