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
Required: Yesarray
Required: Yesx
Required: NosigmaZTEST
Required: Yesarray
Required: Yesx
Required: NosigmaT.TEST
Required: Yesarray1
Required: Yesarray2
Required: Yestails
Required: YestypeTTEST
Required: Yesarray1
Required: Yesarray2
Required: Yestails
Required: YestypeCONFIDENCE.T
Required: Yesalpha
Required: Yesstandard_dev
Required: YessizeT.DIST.2T
Required: Yesx
Required: Yesdegrees_freedomT.DIST.RT
Required: Yesx
Required: Yesdegrees_freedomT.INV
Required: Yesprobability
Required: Yesdegrees_freedomT.INV.2T
Required: Yesprobability
Required: Yesdegrees_freedomCHISQ.DIST.RT
Required: Yesx
Required: Yesdegrees_freedomCHISQ.INV
Required: Yesprobability
Required: Yesdegrees_freedomCHISQ.INV.RT
Required: Yesprobability
Required: Yesdegrees_freedomFDIST
Required: Yesx
Required: Yesnumerator_df
Required: Yesdenominator_dfF.DIST.RT
Required: Yesx
Required: Yesnumerator_df
Required: Yesdenominator_dfFINV
Required: Yesprobability
Required: Yesnumerator_df
Required: Yesdenominator_dfFINV.RT
Required: Yesprobability
Required: Yesnumerator_df
Required: Yesdenominator_df
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
Z.TEST | decimal | The chance of an average at least this high if the true average is x, from 0 to 1 | Z.TEST([52, 48, 61, 55], 50) |
ZTEST | decimal | The same as Z.TEST | ZTEST([52, 48, 61, 55], 50, 6) |
T.TEST | decimal | The chance of a difference this large by luck, from 0 to 1 | T.TEST([48, 52, 45], [53, 58, 47], 2, 1) |
TTEST | decimal | The same as T.TEST | TTEST([48, 52, 45], [53, 58, 47], 2, 3) |
CONFIDENCE.T | decimal | The margin to add and subtract around the average, from the t distribution | CONFIDENCE.T(0.05, 12.5, 15) |
T.DIST.2T | decimal | The chance of a t value this far from 0 in either direction | T.DIST.2T(2.3, 14) |
T.DIST.RT | decimal | The chance of a t value this high or higher | T.DIST.RT(2.3, 14) |
T.INV | decimal | The t value with that chance to its left | T.INV(0.95, 14) |
T.INV.2T | decimal | The t value that leaves that chance outside it, in both ends together | T.INV.2T(0.05, 14) |
CHISQ.DIST.RT | decimal | The chance of a chi-square value this high or higher | CHISQ.DIST.RT(7.8, 3) |
CHISQ.INV | decimal | The chi-square value with that chance to its left | CHISQ.INV(0.95, 3) |
CHISQ.INV.RT | decimal | The chi-square value with that chance to its right | CHISQ.INV.RT(0.05, 3) |
FDIST | decimal | The chance of an F value this high or higher | FDIST(3.1, 4, 20) |
F.DIST.RT | decimal | The same as FDIST | F.DIST.RT(3.1, 4, 20) |
FINV | decimal | The F value with that chance to its right | FINV(0.05, 4, 20) |
FINV.RT | decimal | The same as FINV. The spelling F.INV.RT is also accepted | FINV.RT(0.05, 4, 20) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | An 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 range | Pass numbers in range |
Limitations
T.TESTwith 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.INVandT.INV.2Tneed a chance strictly between 0 and 1.T.DIST.2Tneeds a t value of 0 or more.FDIST,F.DIST.RTandFINVtake whole degrees of freedom of at least 1;FDISTandF.DIST.RTneed an F value of 0 or more.CONFIDENCE.Trefuses a sample size under 2, andCHISQ.INV.RTa 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.