33 Miners

The CNN banner said “21st Miner Rescued.” To be on topic, I wondered how Excel might automate that. You need a simple algorithm that checks the count mod 10 and the count mod 100. If i mod 10 = 1 and i mod 100 <> 11 then the appropriate suffix for i is “st”. Similar analyses work for i mod 10 = 2 and i mod 10 = 3. For all other cases (4, 11, 12, 13, 14, etc) the suffix is “th.” If you take control of the status bar, it looks like this.

Sub Miners()
   Dim i As Long
   Dim suffix As String
   Application.DisplayStatusBar = True
   For i = 1 To 33
      If i Mod 10 = 1 And i Mod 100 <> 11 Then
         suffix = “st “
      ElseIf i Mod 10 = 2 And i Mod 100 <> 12 Then
         suffix = “nd “
      ElseIf i Mod 10 = 3 And i Mod 100 <> 13 Then
         suffix = “rd “
         suffix = “th “
      End If
      Application.StatusBar = i & suffix & “miner rescued!”
      Wait (0.8)
   Next i
End Sub

Sub Wait(t As Single)
   Dim sTime As Single
   sTime = Timer + t
   Do While Timer < sTime
End Sub

That’s enough on topic. Here’s a loud cheer for the NASA system engineers the spec’d out the capsule, and another one for the Chilean navy that built it.


Posted in Uncategorized

12 thoughts on “33 Miners

  1. Yes, it was very spectacular rescue. The movie wont do the event justice!

    Here’s a dirtier version:

    Function ist(lng As Long) As String
        Select Case Right(0 & lng, 2)
            Case 11, 12, 13: ist = “th”
            Case Else
                Select Case Right(lng, 1)
                    Case 1: ist = “st”
                    Case 2: ist = “nd”
                    Case 3: ist = “rd”
                    Case Else: ist = “th”
                End Select
        End Select
    End Function

    Count the implicit conversions :)

  2. Something you and your readers may find interesting… here is a one-liner function and a very short formula for generating ordinal suffixes onto numbers that I have posted to the old newsgroups in the past…

    Function Ordinal(Number As Long) As String
      Ordinal = Number & Mid$(“thstndrdthththththth”, 1 – 2 * ((Number) Mod 10) * (Abs((Number) Mod 100 – 12) > 1), 2)
    End Function
  3. Well Rob, I’ll pick up the Spanish glove….


    the single letter option is easier, it’s just “o” or “a”, depending on the gender (Remember that numbers in Spanish do have a Gender!!”)


  4. To make the Spanish glove work on the American localization ‘glove’ you need to use commas.


    This is a very useful topic and discussion. I hope others add more for other international solutions.


  5. @Martin,

    I don’t speak Spanish, so I don’t know what the ordinal suffixes are for it, but since you are using the formula I posted as a basis, that text string (“morodorotototomovono”) will not work correctly. My formula is designed to pick the characters out of the text string two-at-a-time, up to a total of ten characters maximum… yours is twenty characters long, so the last half of it will never be selected. In the English version, the first two characters are for zero (0th), the next three for one, two and three (1st, 2nd and 3rd) and the last two characters for everything else up to nineteen (the rest, up to ninety-nine, repeat only the first ten digits worth… then at the one-hundred point, everything repeats over again).

  6. INDEX({“st”,”nd”,”rd”,”th”},MAX(4*OR(n={11,12,13}),MIN(MOD(n-1,10)+1,4)))

    This is a CS101 problem. Treat 11, 12 and 13 as special cases using “th”, then calculate n MOD 10 once.

    As for VBA, isn’t this a perfect example of when to use Select rather than multiple Ifs?

    Function f(ByVal n As Double) As String
      n = Fix(n)

      Select Case n Mod 10
      Case 0, 4 To 9
        f = “th”
      Case 1
        f = IIf(n <> 11, “st”, “th”)
      Case 2
        f = IIf(n <> 12, “nd”, “th”)
      Case 3
        f = IIf(n <> 13, “rd”, “th”)
      End Select

    End Function

  7. @fzz: Why not f = IIf(n = 11, "th", "st")? Both clauses will be evaluated in all cases, there's no short circuit.

  8. OK good point about n > 100.


    Function f(n As Double) As String
      n = Fix(n)

      If Abs((n Mod 100) – 12) <= 1 Then f = “th”: Exit Function

      Select Case n Mod 10
      Case 1
        f = “st”
      Case 2
        f = “nd”
      Case 3
        f = “rd”
      Case Else
        f = “th”
      End Select
    End Function

  9. Function f(n) As String
      f = Switch(InStr(“123”, n Mod 10) > 0 And (n Mod 100) 10 <> 1, Choose(n Mod 10, “st”, “nd”, “rd”), n > 0, “th”)
    End Function

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.