| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ordinal Numbers
http://www.cpearson.com/excel/ordinal.htm
=A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) I like the fact that this formula uses CHOOSE(). But I have a problem with =10 and <=14. I am more inclined to use =11 and <=13. So I change the formula to the following. =A1&IF(AND(MOD(A1,100)=11,MOD(A1,100)<=13),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) It works fine and makes me feel *logical*. Does anyone see any reason why I shouldn't change to 11 and 13 respectively? Thanks. Epinn |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replace Old Part Numbers with New Part Numbers in a Macro. | Excel Discussion (Misc queries) | |||
| Format cells to display ordinal numbers ex 21st | Excel Worksheet Functions | |||
| conditional formatting | Excel Worksheet Functions | |||
| How to generate sets of random numbers without having duplicates | Excel Worksheet Functions | |||
| Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
Threaded Mode
