Formula Watch - Convert numbers to and from Roman numerals

Formula Watch - Convert numbers to and from Roman numerals

  • Comments 1
  • Likes

Once you get past 20 or so, converting Roman numerals to Arabic (or vice versa) can be tricky. But you can save time and improve accuracy with a pair of Excel functions.

To convert an Arabic number to its classic Roman numeral, use:

=ROMAN(A1)

Naturally, you can place a number directly into the parentheses or choose any cell in your worksheet to reference - as long as the value is between 1 and 3999.

Turning Roman numbers into Arabic is slightly trickier, but still pretty simple. Paste in this formula:

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

But don't hit Enter. Instead, use Ctrl-Shift-Enter. This makes it an array formula, which should handily make the conversion as long as your Roman numeral value is in the same 1 to 3999 range.

Here are some examples of these functions in action:

Roman numeral conversion formulas

Suzanne

Comments
  • Doesn't seem to work in openofffice Calc, probably because this sense of INDIRECT() is an undocumented dingus in Excel.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment