Here’s how to insert Olympic style rankings into Microsoft Excel with Gold, Silver, Bronze and fourth place in names with equal/joint placings noted.
The custom VBA is similar to other ranking functions at Many Ordinal RANK() options in Excel with joint, equal rankings, words and more.
Use this to make the right wording for a published table of results, output to a web page or data source for a Word mail merge (of certificates etc.).
Add this custom function RankOlympic() passing in the ranking, Highest value ranking and how many entries have that ranking.
Function RankOlympic(pNumber As Integer, pTopRank As Integer, pNumEqualRankings As Integer) As String
' Office-Watch.com
' Olympic style rankings.
Select Case pNumber
Case 1
RankOlympic = "Gold"
Case 2
RankOlympic = "Silver"
Case 3
RankOlympic = "Bronze"
Case 4
RankOlympic = "Just missed out"
Case Else
RankOlympic = "one of the rest"
End Select
' For bottom ranked item
If pNumber = pTopRank Then
RankOlympic = "Last of the rest"
End If
' returns a suffix depending on the number of equal rankings.
Select Case pNumEqualRankings
Case 1
' nothing to do
Case 2
RankOlympic = RankOlympic & " (joint)"
Case Else
RankOlympic = RankOlympic & " (equal)"
End Select
End Function
