Creating Two Proportion Test with LAMBDA

What is Two Proportion Test

Two Proportion Test compares one proportion to determine if it is equal to, lesser than or greater than another proportion.

Unlike One Proportion Test, the size of the second proportion can have an effect on the results.

Suppose you gave flavour X ice-cream to two groups of people. In the first group 65 of 100 people liked flavour X, and in the second group 90 of 130 people liked. Did this two groups responded similarly?

We write our hypothesis statement as the "Did the two groups responded similiarly to flavour X ice-cream?" The Null and Alternative Hypothesis is written as:

`H_0: p_1 = p_2`
`H_1: p_1 != p_2`

where

`p_1` is proportion from group 1

`p_2` is proportion from group 2

Test Statistics

The equation of a two proportion test statistics is written as

`z = (p_1 - p_2) / sqrt( hat p(1 - hat p)(1/n_1 + 1 / n_2) )`

where the pooled variance

`hat p = (o_1 + o_2)/(n_1 + n_2)`

and

`p_1 = o_1 / n_1` is the proportion from group 1
`p_2 = o_2 / n_2` is the proportion from group 2
`o_1` is the number of likes from group 1
`o_2` is the number of likes from group 2
`n_1` is the number of people in group 1
`n_2` is the number of people in group 2

p-value

The left-tailed p-value is calculated in the same was as in the One Proportion Test. In Excel, we use the standard normal cumulative distribution function, by passing TRUE to Excel's standard normal distribution NORM.S.DIST.

      pvalueLeftTail, NORM.S.DIST(zStatistics,TRUE),

Likewise, the right-tailed and two-tailed p-values are calculated as

      pvalueRightTail, 1 - pvalueLeftTail,

and

      pvalueTwoTail, 2 * IF(pvalueLeftTail

Implementing as a LAMDBA function

Putting all these together, the Two Proportion Test

dcrProportion.Two.Test
=LAMBDA(observed_size_1, sampled_size_1, observed_size_2, sampled_size_2, [tail], [show_details],
  LET(tail, IF(ISOMITTED(tail), 0, tail),
      show_details, IF(ISOMITTED(show_details), FALSE, show_details),
      proportion_1, observed_size_1/sampled_size_1,
      proportion_2, observed_size_2/sampled_size_2,
      proportion_pooled, (observed_size_1 + observed_size_2) / (sampled_size_1 + sampled_size_2),
      numerator, proportion_1 - proportion_2,
      denominator, SQRT( proportion_pooled * (1 - proportion_pooled) * (1/sampled_size_1 + 1/sampled_size_2) ),
      zStatistics, numerator / denominator,
      pvalueLeftTail, NORM.S.DIST(zStatistics,TRUE),
      pvalueRightTail, 1 - pvalueLeftTail,
      pvalueTwoTail, 2 * IF(pvalueLeftTail P2",
          "Right Tail Test P1 < P2",
          "Two Tail Test P1 = P2"
        ),
        VSTACK(
          observed_size_1,
          sampled_size_1,
          observed_size_2,
          sampled_size_2,
          proportion_1,
          proportion_2,
          zStatistics,
          pvalueLeftTail,
          pvalueRightTail,
          pvalueTwoTail
        )
      ),
      IF(show_details, IFNA(details,""), pvalue)
  )
)

The following screenshots shows the Two Proportion Test in LAMBDA function in action. The default is two-tailed = 0, while left-tailed = -1, and right-tailed = 1.

The results is also displayed in a table when show_details = TRUE. 

The two-tailed p-value 0.497446 > 0.05 shows the result is not statistically significant to reject the Null Hypothesis. The two proportions are statistically equal, and we conclude that the two groups have similar response to flavour X ice-cream.

With both One Proportion and Two Proportion Test, you are now armed with cool statistical tools to start your own research. I will be looking into other hypothesis tests, so stay tuned to DC-DEN!