Created
September 20, 2025 09:25
-
-
Save ArrowstreamUK/d1da249cd3038433b9e3f5d0527523eb to your computer and use it in GitHub Desktop.
An Excel lambda function to calculate a two proportion z-test
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- More robust version with error handling: | |
| CREATE MACRO two_prop_z_test_safe(percent1, percent2, base1, base2) AS ( | |
| CASE | |
| WHEN base1 <= 0 OR base2 <= 0 THEN NULL | |
| WHEN percent1 < 0 OR percent1 > 1 OR percent2 < 0 OR percent2 > 1 THEN NULL | |
| WHEN ((percent1 * (1 - percent1)) / base1) + ((percent2 * (1 - percent2)) / base2) <= 0 THEN NULL | |
| ELSE (percent1 - percent2) / | |
| SQRT( | |
| ((percent1 * (1 - percent1)) / base1) + | |
| ((percent2 * (1 - percent2)) / base2) | |
| ) | |
| END | |
| ); | |
| -- Example with sample data: | |
| WITH test_data AS ( | |
| SELECT | |
| 0.15 AS conversion_rate_a, -- 15% conversion rate | |
| 0.12 AS conversion_rate_b, -- 12% conversion rate | |
| 1000 AS sample_size_a, | |
| 1200 AS sample_size_b | |
| ) | |
| SELECT | |
| conversion_rate_a, | |
| conversion_rate_b, | |
| sample_size_a, | |
| sample_size_b, | |
| two_prop_z_test(conversion_rate_a, conversion_rate_b, sample_size_a, sample_size_b) AS z_score, | |
| -- Interpret the result | |
| CASE | |
| WHEN ABS(two_prop_z_test(conversion_rate_a, conversion_rate_b, sample_size_a, sample_size_b)) > 1.96 | |
| THEN 'Significant at 95% level' | |
| ELSE 'Not significant at 95% level' | |
| END AS significance_95 | |
| FROM test_data; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment