Skip to main content

Statistical formulas

This documentation describes various statistical analysis formulas that are available in our spreadsheet application. Each of these formulas have certain parameters that can be passed as inputs to generate the required outputs.

The formulas are grouped by the statistical test they belong to, and each group is accompanied by a general description of the test. Each formula also includes its syntax, a brief description, an example usage, an example result, and details about its parameters.

Table of contents

One-factorial ANOVA

The one-factorial anova aims to determine if the means of k-independent samples of a normal distribution and equal variances are equal (null hypothesis). The alternative hypothesis states that there is at least one pair of samples with statistically different means. The samples are given in subsequent Range k columns.

ANOVA.FSCORE()

Syntax:

ANOVA.FSCORE(Range 1, Range 2, ..., Range n)

About:

Returns the f-score of an anova for a given set of samples. The one-factorial anova aims to determine if the means of k-independent samples of a normal distribution and equal variances are equal (null hypothesis). The alternative hypothesis states that there is at least one pair of samples with statistically different means. The samples are given in subsequent Range k columns.

Example:

=ANOVA.FSCORE([1,2], [3,4])

Result example:

8

Parameters:

  • Range: Range 1: The first sample/mandatory
  • Range: Range 2: The second sample/mandatory
  • : ...
  • Range: Range n: The nth sample/mandatory

ANOVA.FTEST()

Syntax:

ANOVA.FTEST(Range 1, Range 2, ..., Range n)

About:

Returns the p-value for a f-score of an anova for a given set of samples. The one-factorial anova aims to determine if the means of k-independent samples of a normal distribution and equal variances are equal (null hypothesis). The alternative hypothesis states that there is at least one pair of samples with statistically different means. The samples are given in subsequent Range k columns.

Example:

=ANOVA.FTEST([1,2], [3,4])

Result example:

0.10557…

Parameters:

  • Range: Range 1: The first sample/mandatory
  • Range: Range 2: The second sample/mandatory
  • : ...
  • Range: Range n: The nth sample/mandatory

Barnard's exact test

Barnard's exact test is a nonparametric, statistical test used to check for independence between two nominal, dichotomous (i.e. no more than two manifestations) variables stored in the variables firstColumn and secondColumn (the null hypothesis). The result of the test provides the exact probability that particular observations in the given data could occur if the null hypothesis of independence between the variables is true. This test is considered a more powerful alternative to Fisher's exact test, but it is computationally more resource-demanding. As a result, if the total number of observations exceeds the value set for the maxBarnardsN setting (default: 200) when initializing this library, this method will return undefined and log an error message. This value can be changed during the initialization of the library.

BARNARDSTEST.WALD()

Syntax:

BARNARDSTEST.WALD(firstColumn → Range, secondColumn → Range)

About:

The Wald statistic calculated for a given sample. Barnard's exact test is a nonparametric, statistical test used to check for independence between two nominal, dichotomous (i.e. no more than two manifestations) variables stored in the variables firstColumn and secondColumn (the null hypothesis). The result of the test provides the exact probability that particular observations in the given data could occur if the null hypothesis of independence between the variables is true. This test is considered a more powerful alternative to Fisher's exact test, but it is computationally more resource-demanding. As a result, if the total number of observations exceeds the value set for the maxBarnardsN setting (default: 200) when initializing this library, this method will return undefined and log an error message. This value can be changed during the initialization of the library.

Example:

=BARNARDSTEST.WALD(["female","female","female","female","female","female","female","female","male","male","male","male","male","male","male","male","male"],["yes","yes","no","no","no","no","no","no","yes","yes","yes","yes","yes","yes","yes","no","no"])

Result example:

-2.17608…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

BARNARDSTEST.NUISANCE()

Syntax:

BARNARDSTEST.NUISANCE(firstColumn → Range, secondColumn → Range)

About:

The nuisance parameter (for which this significance is largest). Barnard's exact test is a nonparametric, statistical test used to check for independence between two nominal, dichotomous (i.e. no more than two manifestations) variables stored in the variables firstColumn and secondColumn (the null hypothesis). The result of the test provides the exact probability that particular observations in the given data could occur if the null hypothesis of independence between the variables is true. This test is considered a more powerful alternative to Fisher's exact test, but it is computationally more resource-demanding. As a result, if the total number of observations exceeds the value set for the maxBarnardsN setting (default: 200) when initializing this library, this method will return undefined and log an error message. This value can be changed during the initialization of the library.

Example:

=BARNARDSTEST.NUISANCE(["female","female","female","female","female","female","female","female","male","male","male","male","male","male","male","male","male"],["yes","yes","no","no","no","no","no","no","yes","yes","yes","yes","yes","yes","yes","no","no"])

Result example:

0.50001…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

BARNARDSTEST.P1TAILED()

Syntax:

BARNARDSTEST.P1TAILED(firstColumn → Range, secondColumn → Range)

About:

The one-tailed significance. Barnard's exact test is a nonparametric, statistical test used to check for independence between two nominal, dichotomous (i.e. no more than two manifestations) variables stored in the variables firstColumn and secondColumn (the null hypothesis). The result of the test provides the exact probability that particular observations in the given data could occur if the null hypothesis of independence between the variables is true. This test is considered a more powerful alternative to Fisher's exact test, but it is computationally more resource-demanding. As a result, if the total number of observations exceeds the value set for the maxBarnardsN setting (default: 200) when initializing this library, this method will return undefined and log an error message. This value can be changed during the initialization of the library.

Example:

=BARNARDSTEST.P1TAILED(["female","female","female","female","female","female","female","female","male","male","male","male","male","male","male","male","male"],["yes","yes","no","no","no","no","no","no","yes","yes","yes","yes","yes","yes","yes","no","no"])

Result example:

0.02452…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

BARNARDSTEST.P2TAILED()

Syntax:

BARNARDSTEST.P2TAILED(firstColumn → Range, secondColumn → Range)

About:

The two-tailed significance. Barnard's exact test is a nonparametric, statistical test used to check for independence between two nominal, dichotomous (i.e. no more than two manifestations) variables stored in the variables firstColumn and secondColumn (the null hypothesis). The result of the test provides the exact probability that particular observations in the given data could occur if the null hypothesis of independence between the variables is true. This test is considered a more powerful alternative to Fisher's exact test, but it is computationally more resource-demanding. As a result, if the total number of observations exceeds the value set for the maxBarnardsN setting (default: 200) when initializing this library, this method will return undefined and log an error message. This value can be changed during the initialization of the library.

Example:

=BARNARDSTEST.P2TAILED(["female","female","female","female","female","female","female","female","male","male","male","male","male","male","male","male","male"],["yes","yes","no","no","no","no","no","no","yes","yes","yes","yes","yes","yes","yes","no","no"])

Result example:

0.04904…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

Binomial test

The binomial test is a nonparametric statistical test used to determine if a dichotomous (i.e. no more than two manifestations) sample distribution deviates significantly from a theoretical binomial distribution. The sample is obtained from a series of independent experiments, where the result can only fall into one of two categories, and the probability of obtaining a specific valueToTest is equal to alpha. The null hypothesis of the test is that the sample is binomially distributed, with success understood as obtaining value equal to valueToTest and with corresponding probability equal to alpha.

BINOMIALTEST.PEXACTLY()

Syntax:

BINOMIALTEST.PEXACTLY(Range, valueToTest, [alpha=0.5])

About:

Returns the probability of obtaining a result that shows the same number of occurrences of a specified value (called "valueToTest") as there are in a given sample. The binomial test is a nonparametric statistical test used to determine if a dichotomous (i.e. no more than two manifestations) sample distribution deviates significantly from a theoretical binomial distribution. The sample is obtained from a series of independent experiments, where the result can only fall into one of two categories, and the probability of obtaining a specific valueToTest is equal to alpha. The null hypothesis of the test is that the sample is binomially distributed, with success understood as obtaining value equal to valueToTest and with corresponding probability equal to alpha.

Example:

=BINOMIALTEST.PEXACTLY(["no","no","no","no","no","no","no","yes","yes","yes","yes","yes"], "yes", 0.8)

Result example:

0.00332…

Parameters:

  • Range: sample: The sample/mandatory
  • String: valueToTest: The value to test/mandatory
  • Decimal: alpha: The probability of obtaining the valueToTest/optional, default=0.5
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

BINOMIALTEST.PFEWER()

Syntax:

BINOMIALTEST.PFEWER(Range, valueToTest, [alpha=0.5])

About:

Returns the probability of obtaining a result that shows a fewer number of occurrences of a specified value (called "valueToTest") as there are in a given sample. The binomial test is a nonparametric statistical test used to determine if a dichotomous (i.e. no more than two manifestations) sample distribution deviates significantly from a theoretical binomial distribution. The sample is obtained from a series of independent experiments, where the result can only fall into one of two categories, and the probability of obtaining a specific valueToTest is equal to alpha. The null hypothesis of the test is that the sample is binomially distributed, with success understood as obtaining value equal to valueToTest and with corresponding probability equal to alpha.

Example:

=BINOMIALTEST.PFEWER(["no","no","no","no","no","no","no","yes","yes","yes","yes","yes"], "yes", 0.8)

Result example:

0.00058…

Parameters:

  • Range: sample: The sample/mandatory
  • String: valueToTest: The value to test/mandatory
  • Decimal: alpha: The probability of obtaining the valueToTest/optional, default=0.5
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

BINOMIALTEST.PATMOST()

Syntax:

BINOMIALTEST.PATMOST(Range, valueToTest, [alpha=0.5])

About:

Returns the probability of obtaining a result that shows a fewer or the same number of occurrences of a specified value (called "valueToTest") as there are in a given sample. The binomial test is a nonparametric statistical test used to determine if a dichotomous (i.e. no more than two manifestations) sample distribution deviates significantly from a theoretical binomial distribution. The sample is obtained from a series of independent experiments, where the result can only fall into one of two categories, and the probability of obtaining a specific valueToTest is equal to alpha. The null hypothesis of the test is that the sample is binomially distributed, with success understood as obtaining value equal to valueToTest and with corresponding probability equal to alpha.

Example:

=BINOMIALTEST.PATMOST(["no","no","no","no","no","no","no","yes","yes","yes","yes","yes"], "yes", 0.8)

Result example:

0.00390…

Parameters:

  • Range: sample: The sample/mandatory
  • String: valueToTest: The value to test/mandatory
  • Decimal: alpha: The probability of obtaining the valueToTest/optional, default=0.5
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

BINOMIALTEST.PMORE()

Syntax:

BINOMIALTEST.PMORE(Range, valueToTest, [alpha=0.5])

About:

Returns the probability of obtaining a result that shows the greater number of occurrences of a specified value (called "valueToTest") as there are in a given sample. The binomial test is a nonparametric statistical test used to determine if a dichotomous (i.e. no more than two manifestations) sample distribution deviates significantly from a theoretical binomial distribution. The sample is obtained from a series of independent experiments, where the result can only fall into one of two categories, and the probability of obtaining a specific valueToTest is equal to alpha. The null hypothesis of the test is that the sample is binomially distributed, with success understood as obtaining value equal to valueToTest and with corresponding probability equal to alpha.

Example:

=BINOMIALTEST.PMORE(["no","no","no","no","no","no","no","yes","yes","yes","yes","yes"], "yes", 0.8)

Result example:

0.99609…

Parameters:

  • Range: sample: The sample/mandatory
  • String: valueToTest: The value to test/mandatory
  • Decimal: alpha: The probability of obtaining the valueToTest/optional, default=0.5
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

BINOMIALTEST.PATLEAST()

Syntax:

BINOMIALTEST.PATLEAST(Range, valueToTest, [alpha=0.5])

About:

Returns the probability of obtaining a result that shows the same or greater number of occurrences of a specified value (called "valueToTest") as there are in a given sample. The binomial test is a nonparametric statistical test used to determine if a dichotomous (i.e. no more than two manifestations) sample distribution deviates significantly from a theoretical binomial distribution. The sample is obtained from a series of independent experiments, where the result can only fall into one of two categories, and the probability of obtaining a specific valueToTest is equal to alpha. The null hypothesis of the test is that the sample is binomially distributed, with success understood as obtaining value equal to valueToTest and with corresponding probability equal to alpha.

Example:

=BINOMIALTEST.PATLEAST(["no","no","no","no","no","no","no","yes","yes","yes","yes","yes"], "yes", 0.8)

Result example:

0.99941…

Parameters:

  • Range: sample: The sample/mandatory
  • String: valueToTest: The value to test/mandatory
  • Decimal: alpha: The probability of obtaining the valueToTest/optional, default=0.5
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

Chi-squared test

The Chi-squared test is a statistical, nonparametric test used to check if the two ordinal or nominal variables (given in firstColumn and secondColumn) are independent (null hypothesis). Its result gives the exact probability that the particular observations in the given data can be made if the null hypothesis holds true.

CHITEST.PCHISQUARED()

Syntax:

CHITEST.PCHISQUARED(firstColumn → Range, secondColumn → Range)

About:

The value of chi-square statistic calculated based on samples. The Chi-squared test is a statistical, nonparametric test used to check if the two ordinal or nominal variables (given in firstColumn and secondColumn) are independent (null hypothesis). Its result gives the exact probability that the particular observations in the given data can be made if the null hypothesis holds true

Example:

=CHITEST.PCHISQUARED(A1:A6,B1:B6)

Result example:

N/A

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

CHITEST.SIGNIFICANCE()

Syntax:

CHITEST.SIGNIFICANCE(firstColumn → Range, secondColumn → Range)

About:

The exact probability that the particular observations in the given data can be made if the null hypothesis that both variables are independent of one another holds true; The Chi-squared test is a statistical, nonparametric test used to check if the two ordinal or nominal variables (given in firstColumn and secondColumn) are independent (null hypothesis). Its result gives the exact probability that the particular observations in the given data can be made if the null hypothesis holds true

Example:

=CHITEST.SIGNIFICANCE(A1:A6,B1:B6)

Result example:

N/A

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

CHITEST.DF()

Syntax:

CHITEST.DF(firstColumn → Range, secondColumn → Range)

About:

Returns the degrees of freedom based on given samples. The Chi-squared test is a statistical, nonparametric test used to check if the two ordinal or nominal variables (given in firstColumn and secondColumn) are independent (null hypothesis). Its result gives the exact probability that the particular observations in the given data can be made if the null hypothesis holds true

Example:

=CHITEST.DF(A1:A6,B1:B6)

Result example:

N/A

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

Fisher's exact test

Gives the exact probability that the particular observations in the given data can be made if the null hypothesis that both variables are independent from another holds true.\nThe Fisher's exact test is a nonparametric, statistical test used to check if two nominal, dichotomous (i.e. no more than two manifestations) variables (given in firstColumn and secondColumn) are independent. Its result gives the exact probability that the particular observations in the given data can be made if the null hypothesis holds true. In the example given below, it means that if men and women were equally likely to be smokers there is about 4.9% chance that the supplied observations stated in testData can be made. If the level of significance (expressed as the p-value) was agreed to be larger than this value, then the data suggest that this hypothesis of equal likelihood to be a smoker should be rejected.

FISHERSEXACTTEST()

Syntax:

FISHERSEXACTTEST(firstColumn → Range, secondColumn → Range)

About:

Gives the exact probability that the particular observations in the given data can be made if the null hypothesis that both variables are independent from another holds true. The Fisher's exact test is a nonparametric, statistical test used to check if two nominal, dichotomous (i.e. no more than two manifestations) variables (given in firstColumn and secondColumn) are independent. Its result gives the exact probability that the particular observations in the given data can be made if the null hypothesis holds true. In the example given below, it means that if men and women were equally likely to be smokers there is about 4.9% chance that the supplied observations stated in testData can be made. If the level of significance (expressed as the p-value) was agreed to be larger than this value, then the data suggest that this hypothesis of equal likelihood to be a smoker should be rejected.

Example:

=FISHERSEXACTTEST(["female","female","female","female","female","female","female","female","male","male","male","male","male","male","male","male","male"],["yes","yes","no","no","no","no","no","no","yes","yes","yes","yes","yes","yes","yes","no","no"])

Result example:

0.04146…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

Mann-Whitney U test

The Mann-Whitney U test is a nonparametric statistical test used to determine if the ranked sums in two subsets of a set are significantly different. The firstColumn of the data contains sample items with two possible values, where items with the same value belong to the same subset. The secondColumn of the data contains an ordinal variable that is used to rank the items (from 1 to n, where n is the sample size of the first column). The null hypothesis states that there is no significant difference between the rank sums of the secondColumn for the two subsets defined by the firstColumn . To test this hypothesis, the test calculates the rank sums for each subset, and then computes the U-statistic and corresponding z-score. Both the U-statistic and z-score are normally distributed, with the z-score having a standard normal distribution. It is important to note that the assumption of normal distribution in the second column variable for the subsets is not required.

MANNWUTEST.ZSCORE()

Syntax:

MANNWUTEST.ZSCORE(firstColumn → Range, secondColumn → Range)

About:

Returns zScore value of Mann Whitney U-statistic. The Mann-Whitney U test is a nonparametric statistical test used to determine if the ranked sums in two subsets of a set are significantly different. The firstColumn of the data contains sample items with two possible values, where items with the same value belong to the same subset. The secondColumn of the data contains an ordinal variable that is used to rank the items (from 1 to n, where n is the sample size of the first column). The null hypothesis states that there is no significant difference between the rank sums of the secondColumn for the two subsets defined by the firstColumn . To test this hypothesis, the test calculates the rank sums for each subset, and then computes the U-statistic and corresponding z-score. Both the U-statistic and z-score are normally distributed, with the z-score having a standard normal distribution. It is important to note that the assumption of normal distribution in the second column variable for the subsets is not required.

Example:

=MANNWUTEST.ZSCORE(["MALE", "FEMALE", "MALE", "FEMALE", "FEMALE", "MALE", "FEMALE", "FEMALE", "FEMALE", "MALE"],[2150, 1800, 2300, 1600, 1700, 2000, 1850, 2200, 1750, 2050])

Result example:

-1.91880…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

MANNWUTEST.P1TAILED()

Syntax:

MANNWUTEST.P1TAILED(firstColumn → Range, secondColumn → Range)

About:

Returns p-value of Mann Whitney U-statistic for one-sided null hypothesis. The Mann-Whitney U test is a nonparametric statistical test used to determine if the ranked sums in two subsets of a set are significantly different. The firstColumn of the data contains sample items with two possible values, where items with the same value belong to the same subset. The secondColumn of the data contains an ordinal variable that is used to rank the items (from 1 to n, where n is the sample size of the first column). The null hypothesis states that there is no significant difference between the rank sums of the secondColumn for the two subsets defined by the firstColumn . To test this hypothesis, the test calculates the rank sums for each subset, and then computes the U-statistic and corresponding z-score. Both the U-statistic and z-score are normally distributed, with the z-score having a standard normal distribution. It is important to note that the assumption of normal distribution in the second column variable for the subsets is not required.

Example:

=MANNWUTEST.P1TAILED(["MALE", "FEMALE", "MALE", "FEMALE", "FEMALE", "MALE", "FEMALE", "FEMALE", "FEMALE", "MALE"],[2150, 1800, 2300, 1600, 1700, 2000, 1850, 2200, 1750, 2050])

Result example:

0.02749…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

MANNWUTEST.P2TAILED()

Syntax:

MANNWUTEST.P2TAILED(firstColumn → Range, secondColumn → Range)

About:

Returns p-value of Mann Whitney U-statistic for two-sided null hypothesis. The Mann-Whitney U test is a nonparametric statistical test used to determine if the ranked sums in two subsets of a set are significantly different. The firstColumn of the data contains sample items with two possible values, where items with the same value belong to the same subset. The secondColumn of the data contains an ordinal variable that is used to rank the items (from 1 to n, where n is the sample size of the first column). The null hypothesis states that there is no significant difference between the rank sums of the secondColumn for the two subsets defined by the firstColumn . To test this hypothesis, the test calculates the rank sums for each subset, and then computes the U-statistic and corresponding z-score. Both the U-statistic and z-score are normally distributed, with the z-score having a standard normal distribution. It is important to note that the assumption of normal distribution in the second column variable for the subsets is not required.

Example:

=MANNWUTEST.P2TAILED(["MALE", "FEMALE", "MALE", "FEMALE", "FEMALE", "MALE", "FEMALE", "FEMALE", "FEMALE", "MALE"],[2150, 1800, 2300, 1600, 1700, 2000, 1850, 2200, 1750, 2050])

Result example:

0.05499…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

Sign test

The sign test is a nonparametric statistical method used to test for consistent differences between pairs of observations. It involves comparing pairs of observations for each subject and determining if one member of the pair tends to be greater or less than the other member (referred to as positives or negatives respectively). Therefore it's important to note that the order of these variables in the sample matters, and all calculated significance parameters should be interpreted with respect to the number of positives assuming the null hypothesis of equal likelihood of positive/negative yield, which follows a binomial distribution with alpha equal to 0.5.

SIGNTEST.POSITIVES()

Syntax:

SIGNTEST.POSITIVES(firstColumn → Range, secondColumn → Range)

About:

Returns the number of pairs where the value for firstColumn is larger than that for secondColumn. The sign test is a nonparametric statistical method used to test for consistent differences between pairs of observations. It involves comparing pairs of observations for each subject and determining if one member of the pair tends to be greater or less than the other member (referred to as positives or negatives respectively). Therefore it's important to note that the order of these variables in the sample matters, and all calculated significance parameters should be interpreted with respect to the number of positives assuming the null hypothesis of equal likelihood of positive/negative yield, which follows a binomial distribution with alpha equal to 0.5.

Example:

=SIGNTEST.POSITIVES([80, 78, 92, 76, 94, 93, 82, 104, 74, 84],[83, 82, 94, 64, 96, 97, 83, 87, 77, 85])

Result example:

2

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

SIGNTEST.PEXACTLY()

Syntax:

SIGNTEST.PEXACTLY(firstColumn → Range, secondColumn → Range)

About:

Calculates the probability of obtaining a result that shows the same number of occurrences of positives (where the value in the first column is greater than the value in the second column) as there are positive cases in a given sample. The sign test is a nonparametric statistical method used to test for consistent differences between pairs of observations. It involves comparing pairs of observations for each subject and determining if one member of the pair tends to be greater or less than the other member (referred to as positives or negatives respectively). Therefore it's important to note that the order of these variables in the sample matters, and all calculated significance parameters should be interpreted with respect to the number of positives assuming the null hypothesis of equal likelihood of positive/negative yield, which follows a binomial distribution with alpha equal to 0.5.

Example:

=SIGNTEST.PEXACTLY([80, 78, 92, 76, 94, 93, 82, 104, 74, 84],[83, 82, 94, 64, 96, 97, 83, 87, 77, 85])

Result example:

0.04394…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

SIGNTEST.PFEWER()

Syntax:

SIGNTEST.PFEWER(firstColumn → Range, secondColumn → Range)

About:

Calculates the probability of obtaining a result that shows the lower number of occurrences of positives (where the value in the first column is greater than the value in the second column) as there are positive cases in a given sample. The sign test is a nonparametric statistical method used to test for consistent differences between pairs of observations. It involves comparing pairs of observations for each subject and determining if one member of the pair tends to be greater or less than the other member (referred to as positives or negatives respectively). Therefore it's important to note that the order of these variables in the sample matters, and all calculated significance parameters should be interpreted with respect to the number of positives assuming the null hypothesis of equal likelihood of positive/negative yield, which follows a binomial distribution with alpha equal to 0.5.

Example:

=SIGNTEST.PFEWER([80, 78, 92, 76, 94, 93, 82, 104, 74, 84],[83, 82, 94, 64, 96, 97, 83, 87, 77, 85])

Result example:

0.01074…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

SIGNTEST.PATMOST()

Syntax:

SIGNTEST.PATMOST(firstColumn → Range, secondColumn → Range)

About:

Calculates the probability of obtaining a result that shows the lower or the same number of occurrences of positives (where the value in the first column is greater than the value in the second column) as there are positive cases in a given sample. The sign test is a nonparametric statistical method used to test for consistent differences between pairs of observations. It involves comparing pairs of observations for each subject and determining if one member of the pair tends to be greater or less than the other member (referred to as positives or negatives respectively). Therefore it's important to note that the order of these variables in the sample matters, and all calculated significance parameters should be interpreted with respect to the number of positives assuming the null hypothesis of equal likelihood of positive/negative yield, which follows a binomial distribution with alpha equal to 0.5.

Example:

=SIGNTEST.PATMOST([80, 78, 92, 76, 94, 93, 82, 104, 74, 84],[83, 82, 94, 64, 96, 97, 83, 87, 77, 85])

Result example:

0.05468…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

SIGNTEST.PMORE()

Syntax:

SIGNTEST.PMORE(firstColumn → Range, secondColumn → Range)

About:

Calculates the probability of obtaining a result that shows a greater number of occurrences of positives (where the value in the first column is greater than the value in the second column) as there are positive cases in a given sample. The sign test is a nonparametric statistical method used to test for consistent differences between pairs of observations. It involves comparing pairs of observations for each subject and determining if one member of the pair tends to be greater or less than the other member (referred to as positives or negatives respectively). Therefore it's important to note that the order of these variables in the sample matters, and all calculated significance parameters should be interpreted with respect to the number of positives assuming the null hypothesis of equal likelihood of positive/negative yield, which follows a binomial distribution with alpha equal to 0.5.

Example:

=SIGNTEST.PMORE([80, 78, 92, 76, 94, 93, 82, 104, 74, 84],[83, 82, 94, 64, 96, 97, 83, 87, 77, 85])

Result example:

0.94531…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

SIGNTEST.PATLEAST()

Syntax:

SIGNTEST.PATLEAST(firstColumn → Range, secondColumn → Range)

About:

Calculates the probability of obtaining a result that shows the same or the greater number of occurrences of positives (where the value in the first column is greater than the value in the second column) as there are positive cases in a given sample. The sign test is a nonparametric statistical method used to test for consistent differences between pairs of observations. It involves comparing pairs of observations for each subject and determining if one member of the pair tends to be greater or less than the other member (referred to as positives or negatives respectively). Therefore it's important to note that the order of these variables in the sample matters, and all calculated significance parameters should be interpreted with respect to the number of positives assuming the null hypothesis of equal likelihood of positive/negative yield, which follows a binomial distribution with alpha equal to 0.5.

Example:

=SIGNTEST.PATLEAST([80, 78, 92, 76, 94, 93, 82, 104, 74, 84],[83, 82, 94, 64, 96, 97, 83, 87, 77, 85])

Result example:

0.94531…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Enumerated: scaleOfFirstColumn: The type of scale of the first sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Enumerated: scaleOfSecondColumn: The type of scale of the second sample
    1. 'nominal' - for categorical variables, allows computing mode;
    2. 'ordinal' - allows to sort the values, but not to determine intervals between them or arithmetic operations - a median can be calculated;
    3. 'interval' - allows to calculate a difference between the values, but not to perform arithmetic operations - an average, a standard deviation can be calculated;
    4. 'metric' - allows calculating a product and/or a quotient -> a geometric and a harmonic mean can be calculated; optional, default='metric', possible values: 'nominal', 'ordinal','interval','metric'
  • Decimal: epsilon: The threshold below which further iterative calculation methods are abandoned, optional, default=0.00001
  • Decimal: spougeConstant: Defines the constant a in Spouge’s approximation of the gamma function., optional, default=40

Student's t-test

The objective of the one-sample t-test is to validate if the arithmetic mean of a sample column is equal to the predefined or suspected arithmetic mean nullHypothesisMean (this is the null Hypothesis). It is important to mention that the variable should be normally distributed.

The objective of the two-sample t-test test is to validate if the arithmetic mean of a firstColumn is equal to the arithmetic mean of a secondColumn (this is the null Hypothesis). It is important to mention that the variables should be normally distributed.

TTEST.ONESAMPLE.T()

Syntax:

TTEST.ONESAMPLE.T(sample → Range, [nullHypothesisMean=0])

About:

Returns the T-value for the test. The objective of the test is to validate if the arithmetic mean of a sample column is equal to the predefined or suspected arithmetic mean nullHypothesisMean (this is the null Hypothesis). It is important to mention that the variable should be normally distributed.

Example:

=TTEST.ONESAMPLE.T([185, 201, 193, 184, 180, 176, 193, 182, 197, 204], 200)

Result example:

-3.51631…

Parameters:

  • Range: sample: The sample/mandatory
  • Decimal: nullHypothesisMean: The presumed mean value, optional, default 0

TTEST.ONESAMPLE.P1SIDED()

Syntax:

TTEST.ONESAMPLE.P1SIDED(sample → Range, [nullHypothesisMean=0])

About:

Returns the p-value for one-sided distribution corresponding to obtained T-value provided that the null hypothesis holds true. The objective of the test is to validate if the arithmetic mean of a sample column is equal to the predefined or suspected arithmetic mean nullHypothesisMean (this is the null Hypothesis). It is important to mention that the variable should be normally distributed.

Example:

=TTEST.ONESAMPLE.P1SIDED([185, 201, 193, 184, 180, 176, 193, 182, 197, 204], 200)

Result example:

0.00327…

Parameters:

  • Range: sample: The sample/mandatory
  • Decimal: nullHypothesisMean: The presumed mean value, optional, default 0

TTEST.ONESAMPLE.P2SIDED()

Syntax:

TTEST.ONESAMPLE.P2SIDED(sample → Range, [nullHypothesisMean=0])

About:

Returns the p-value for two-sided distribution corresponding to obtained T-value provided that the null hypothesis holds true. The objective of the test is to validate if the arithmetic mean of a sample column is equal to the predefined or suspected arithmetic mean nullHypothesisMean (this is the null Hypothesis). It is important to mention that the variable should be normally distributed.

Example:

=TTEST.ONESAMPLE.P2SIDED([185, 201, 193, 184, 180, 176, 193, 182, 197, 204], 200)

Result example:

0.00655…

Parameters:

  • Range: sample: The sample/mandatory
  • Decimal: nullHypothesisMean: The presumed mean value, optional, default 0

TTEST.ONESAMPLE.DF()

Syntax:

TTEST.ONESAMPLE.DF(sample → Range, [nullHypothesisMean=0])

About:

Returns degrees of freedom for the sample. The objective of the test is to validate if the arithmetic mean of a sample column is equal to the predefined or suspected arithmetic mean nullHypothesisMean (this is the null Hypothesis). It is important to mention that the variable should be normally distributed.

Example:

=TTEST.ONESAMPLE.DF([185, 201, 193, 184, 180, 176, 193, 182, 197, 204], 200)

Result example:

9

Parameters:

  • Range: sample: The sample/mandatory
  • Decimal: nullHypothesisMean: The presumed mean value, optional, default 0

TTEST.TWOSAMPLES.T()

Syntax:

TTEST.TWOSAMPLES.T(firstColumn → Range, secondColumn → Range, [pairedSamples=false])

About:

Returns the T-value for the test. The objective of the test is to validate if the arithmetic mean of a firstColumn is equal to the arithmetic mean of a secondColumn (this is the null Hypothesis). It is important to mention that the variables should be normally distributed.

Example:

=TTEST.TWOSAMPLES.T([204, 212, 199, 209, 191, 190, 223, 185, 206, 212],[189, 199, 191, 182, 176, 180, 220, 182, 194, 216])

Result example:

1.69068…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Boolean: pairedSamples: The flag to indicate if the samples are dependent, optional, default false

TTEST.TWOSAMPLES.P1SIDED()

Syntax:

TTEST.TWOSAMPLES.P1SIDED(firstColumn → Range, secondColumn → Range, [pairedSamples=false])

About:

Returns the p-value for one-sided distribution corresponding to obtained T-value provided that the null hypothesis holds true. The objective of the test is to validate if the arithmetic mean of a firstColumn is equal to the arithmetic mean of a secondColumn (this is the null Hypothesis). It is important to mention that the variables should be normally distributed.

Example:

=TTEST.TWOSAMPLES.P1SIDED([204, 212, 199, 209, 191, 190, 223, 185, 206, 212],[189, 199, 191, 182, 176, 180, 220, 182, 194, 216])

Result example:

0.05406…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Boolean: pairedSamples: The flag to indicate if the samples are dependent, optional, default false

TTEST.TWOSAMPLES.P2SIDED()

Syntax:

TTEST.TWOSAMPLES.P2SIDED(firstColumn → Range, secondColumn → Range, [pairedSamples=false])

About:

Returns the p-value for two-sided distribution corresponding to obtained T-value provided that the null hypothesis holds true. The objective of the test is to validate if the arithmetic mean of a firstColumn is equal to the arithmetic mean of a secondColumn (this is the null Hypothesis). It is important to mention that the variables should be normally distributed.

Example:

=TTEST.TWOSAMPLES.P2SIDED([204, 212, 199, 209, 191, 190, 223, 185, 206, 212],[189, 199, 191, 182, 176, 180, 220, 182, 194, 216])

Result example:

0.10814…

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Boolean: pairedSamples: The flag to indicate if the samples are dependent, optional, default false

TTEST.TWOSAMPLES.DF()

Syntax:

TTEST.TWOSAMPLES.DF(firstColumn → Range, secondColumn → Range, [pairedSamples=false])

About:

Returns degrees of freedom for the sample. The objective of the test is to validate if the arithmetic mean of a firstColumn is equal to the arithmetic mean of a secondColumn (this is the null Hypothesis). It is important to mention that the variables should be normally distributed.

Example:

=TTEST.TWOSAMPLES.DF([204, 212, 199, 209, 191, 190, 223, 185, 206, 212],[189, 199, 191, 182, 176, 180, 220, 182, 194, 216])

Result example:

18

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Boolean: pairedSamples: The flag to indicate if the samples are dependent, optional, default false

TTEST.TWOSAMPLES.MISSINGS()

Syntax:

TTEST.TWOSAMPLES.MISSINGS(firstColumn → Range, secondColumn → Range, [pairedSamples=false])

About:

Returns the number of missing values (if the dependent parameter is set to true). The objective of the test is to validate if the arithmetic mean of a firstColumn is equal to the arithmetic mean of a secondColumn (this is the null Hypothesis). It is important to mention that the variables should be normally distributed.

Example:

N/A

Result example:

N/A

Parameters:

  • Range: firstColumn: The first sample/mandatory
  • Range: secondColumn: The second sample/mandatory
  • Boolean: pairedSamples: The flag to indicate if the samples are dependent, optional, default false