Roman Numerals

[rhouston]

How do I choose the largest roman numeral from a group of numerals?

i.e. (I, II, V, XII, MXII)

Largest numeral is MXII

 

[Joe_Canadian]

=MATCH(A1,ROMAN(ROW($1:$3999)),0)

Will convert the Roman numeral in cell A1 to arabic numbers (Note: It must be entered as an array formula ctrl+shift+enter instead of enter)

If you use a helper column you can convert from roman to arabic, then use =ROMAN(MAX(B:B)) to get the largest roman numeral (Assuming B is your helper column)

 

[Scott Huish]

ROMAN doesn't convert ROMAN to ARABIC, it converts ARABIC to ROMAN.
Your formula returned 1 from the sample set, not MXII.

Although, perhaps this might work, this of course assumes that the number won't go over 3999:

=ROMAN(MAX(MATCH(A1:A5,ROMAN(ROW($1:$3999)),0)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[Andrew Fergus]

Hi

I like Barry's method of using a formula (Barry is the formula king) but the following VBA code will return the largest roman numeral value without calculating every roman numeral value nor without a limit of 3999 (although it is unlikely you would express a value in the millions using roman numerals). This method also contains a function to convert roman numerals back to decimal:

Option Explicit
 
Public Function MaxRoman(MyRange As Range) As String
Dim CellCount As Long, LoopCount As Long
Dim MyArray() As Variant, cell As Range
CellCount = MyRange.Cells.Count
ReDim MyArray(2, CellCount) As Variant
LoopCount = 0
MyArray(2, 0) = 0
For Each cell In MyRange
    LoopCount = LoopCount + 1
    MyArray(1, LoopCount) = cell.Value
    MyArray(2, LoopCount) = ConvertToDecimal(MyArray(1, LoopCount))
    If MyArray(2, LoopCount) > MyArray(2, 0) Then
        MyArray(2, 0) = MyArray(2, LoopCount)
        MyArray(1, 0) = MyArray(1, LoopCount)
    End If
Next
MaxRoman = MyArray(1, 0)
End Function
 
Public Function ConvertToDecimal(InputValue As Variant) As Long
Dim RunSum As Long, _
    LoopCount As Long, _
    NextChar As String
If Len(InputValue) = 0 Then
    ConvertToDecimal = 0
    Exit Function
End If
RunSum = 0
For LoopCount = 1 To Len(InputValue)
    If LoopCount < Len(InputValue) Then
        NextChar = Mid$(InputValue, LoopCount + 1, 1)
    Else
        NextChar = "I"
    End If
    Select Case Mid$(InputValue, LoopCount, 1)
        Case "I"
            If NextChar <> "I" Then
                RunSum = RunSum - 1
            Else
                RunSum = RunSum + 1
            End If
        Case "V"
            If NextChar <> "I" And NextChar <> "V" Then
                RunSum = RunSum - 5
            Else
                RunSum = RunSum + 5
            End If
        Case "X"
            If NextChar <> "I" And NextChar <> "V" And NextChar <> "X" Then
                RunSum = RunSum - 10
            Else
                RunSum = RunSum + 10
            End If
        Case "L"
            If NextChar = "M" Or NextChar = "D" Or NextChar = "C" Then
                RunSum = RunSum - 50
            Else
                RunSum = RunSum + 50
            End If
        Case "C"
            If NextChar = "M" Or NextChar = "D" Then
                RunSum = RunSum - 100
            Else
                RunSum = RunSum + 100
            End If
        Case "D"
            If NextChar = "M" Then
                RunSum = RunSum - 500
            Else
                RunSum = RunSum + 500
            End If
        Case "M"
            RunSum = RunSum + 1000
        Case Else
            DoEvents
    End Select
Next LoopCount
ConvertToDecimal = RunSum
End Function

You can use the custom function in a formula like this:
=MaxRoman(A1:A5)

Or to just convert a value in roman numerals to decimal, use this instead:
=ConvertToDecimal(A1)

HTH, Andrew