Andrew Le Breuilly ArrowstreamUK
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 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" | |
| ) |