Creating One Proportion Test with LAMBDA

-

What is One Proportion Test

The One Proportion Test compares a sample proportion against a target proportion.

For example, you observe that you get 6 heads when you flip a coin 10 times (sample). You expect with a fair coin, the probability of getting heads is 0.5 or 50% of the time (target). The One Proportion Test allows you to assess if the flipped coin is a fair coin based on the observations. The test proves this by statistically comparing the observed proportion of heads to number of flipping 0.6, is equal to the expected proportion 0.5.

We write the Null Hypothesis as the observed proportion is equal to the hypothesised proportion.

`H_0: p = p_0`

And the Alternative Hypothesis is that the observed proportion is not equal to the hypothesised proportion (two-tailed test).

`H_1: p != p_0`

We could also test if the observed proportion is greater than the hypothesised proportion (left tail test).

`H_0: p >= p_0`

Or if the observed proportion is less than the hypothesised proportion (right tail test).

`H_0: p <= p_0`

Assumptions

For proportion testing we assume:

  1. The samples are completely random
  2. The population follows a binomial distribution
  3. For large sample, the binomial distribution can be approximated by a normal distribution.

For my LAMBDA implementation, I will use assume a large sample is under consideration to assume normal distribution.

Test Statistics

The test statistics for One Proportion Testing is

`z = (p - p_0) / sqrt( (p_0(1 - p_0)) / n)`
where
`z` is the test statistics
`p` is the observed proportion
`p_0` is the hypothesised proportion
`n` is the number of samples taken

Intermediate calculations in LAMBDA

My LAMBDA formula will take three parameters: expected_proportion, observed_size, and sampled_size.

Intermediate values to be calculated

  observed_proportion, observed_size/sampled_size,
  numerator, observed_proportion - expected_proportion,
  denominator, SQRT(expected_proportion * (1-expected_proportion)/sampled_size),
  zStatistics, numerator / denominator,

We can calculate the left tail p-value using standard normal Cumulative Distribution Function. In Excel, we pass TRUE to Excel's standard normal distribution NORM.S.DIST.

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

To calculate the right tail p-value

      pvalueRightTail, 1 - pvalueLeftTail,

And the two tail p-value would be

      pvalueTwoTail, 2 * IF(pvalueLeftTail

Putting it together

We put the calculations together in the following LAMBDA function.

dcrProportion.One.Test
=LAMBDA(expected_proportion, observed_size, sampled_size, [tail], [show_details],
  LET(tail, IF(ISOMITTED(tail), 0, tail),
      show_details, IF(ISOMITTED(show_details), FALSE, show_details),
      observed_proportion, observed_size/sampled_size,
      numerator, observed_proportion - expected_proportion,
      denominator, SQRT(expected_proportion * (1-expected_proportion)/sampled_size),
      zStatistics, numerator / denominator,
      pvalueLeftTail, NORM.S.DIST(zStatistics,TRUE),
      pvalueRightTail, 1 - pvalueLeftTail,
      pvalueTwoTail, 2 * IF(pvalueLeftTail P0",
          "Right Tail Test P < P0",
          "Two Tail Test P = P0"
        ),
        VSTACK(
          expected_proportion,
          observed_size,
          sampled_size,
          observed_proportion,
          zStatistics,
          pvalueLeftTail,
          pvalueRightTail,
          pvalueTwoTail
        )
      ),
      IF(show_details, IFNA(details,""), pvalue)
    )
)

I put in a few optional parameters to overload the function. In the default, the two-tailed p-value is calculated (tail=0). For left-tail = -1, and for right-tail = 1.

If you specify to show to details, you will get the full summary of the One Proportion Test.

From this One Proportion Test, the two-tail p-value is 0.527089 is greater than an alpha of 0.05. We say that the observation is not statistically significant to reject the Null Hypothesis. Hence we accept the Null Hypothesis and conclude that the observed flipped coin is a fair coin.

Observe as well the p-values for Left Tail and Right Tail tests. In the Left Tail test, with the p-value 0.736455 > 0.05, the result is not significant enough to reject the Null Hypothesis. So we accept the Null Hypothesis that `p >= p_0`. Nothing shocking here. We are expecting this, since the proportion 0.6 > 0.5.

In the Right Tail test, with the p-value 0.263545 > 0.05, the result is not significant enough to reject the Null Hypothesis either. So we accept the Null Hypothesis that `p <= p_0`. This is strange, since numerically the proportion 0.6 is NOT < 0.5. That's something to ponder.

If you find this helpful, let me know in the comments below. In the meantime, try out the One Proportion Test in your DC-DEN!

Comments

Post a Comment