Roman Numerals (Excel 2000)

[frankwalsh1962]

I would like to be able to format a column to display the value of it's cell content as roman numeral, so that it reads II, III, IV, etc. The underlying data needs to be in ordinary 'arabic' integer form, but I need to display the number as roman - in the same way as some people put the month in RNs to avoid date confusion ie 4/iv/05. Can we do this in Excel? I looked at format cells but could not see a way. Grateful for any help

[HansV]

There is no roman format, but you can use the ROMAN worksheet function to display roman numbers in an adjacent column, and hide the column with original data.

Say that your numbers are in column A, starting in A2 (with a column header in A1). In B1, enter an appropriate caption, and in B2, enter the formula
=ROMAN(A2)
Fill down as far as needed. You can hide column A now. You would still refer to the cells in column A for calculations; the values in column B are for display only.

[Jezza]

Excel has a great function

=ROMAN(5), this will return V

I have just put a list of numbers from 1 to 100 in column A and put the function in B, it works a treat.

Hope this helps

[frankwalsh1962]

Thanks to both of you. It is as I had feared - I will have to work around using two cols and the =roman() function. Another one for the wishlist!

[sdckapr]

If you want to avoid "date confusion" why not format to a date using "mmm" instead of "mm":
format - cells - Number(tab) custom:
dd/mmm/yy

it will not be a roman, but it will remain a date (and to me, be more "understandable") and not cause confusion.

Steve

[LegareColeman]

The code below, placed in the Worksheet Change event routine in the module behind the worksheet will do what you want in column A. The code would need to be modified to work on a different column or range. Try entering numbers in column A of Sheet1 in the attached workbook.

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each oCell In Intersect(Target, Range("A:A"))
If (oCell.Value <> "" And IsNumeric(oCell.Value)) Then
oCell.Value = Application.WorksheetFunction.Roman(oCell.Value)
End If
Next oCell
Application.EnableEvents = True
End Sub
</pre>

[HansV]

This works well in itself, but it means that the numbers can't be used in calculations any more. The OP stated "The underlying data needs to be in ordinary 'arabic' integer form".

[LegareColeman]

If he needs to do calculations with the values, you are correct. I was reading that to mean he wanted to enter the values as aribic numbers.