Andrew Le Breuilly ArrowstreamUK
ArrowstreamUK
/ two-proportion z-test - duckdb
Created
September 20, 2025 09:25
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) |
ArrowstreamUK
/ ordinal_suffix
Last active
August 9, 2024 14:10
This Excel lambda function provides the text suffix for positive ordinal integer values. It is useful when used with the rank function. So if you add a value of 32 then then output would be "nd". Similarly, 20 -> th, 21 -> st, 22 ->nd etc.
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
| ordinal_suffix=LAMBDA(value, | |
| LET( | |
| mod_value,MOD(value,100), | |
| IF( | |
| (mod_value>=10)*(mod_value<=20),"th", | |
| IF( | |
| (MOD(mod_value,10)>=1)*(MOD(mod_value,10)<=3), | |
| CHOOSE(MOD(mod_value,10),"st","nd","rd"), | |
| "th" | |
| ) |