Andrew Le Breuilly ArrowstreamUK
ArrowstreamUK
/ excel-lambda-ordinal_suffix.txt
Last active
May 24, 2022 14:16
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
| =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" | |
| ) |