Roman To Arabic, And Back Again!

Ever wondered if it was possible to convert Roman numerals to Arabic in Excel?

Given Excel’s proclivity in coming up with formulas that range from the impressively versatile to the mind-bogglingly vague, you’d figure the odds are good, and as you’re about to find out in a minute, you wouldn’t be far off the mark.

But first, a little lesson in what we’re talking about. Arabic numberals are the numbers that you and I use in everyday life. You know, 1,2,3 and so on. Roman numerals are those infuriatingly complicated beasts that seem resolutely indecipherable at the best of times. You know, I, II, III and so on.

I, II and III might not be such a problem, but do you have any clue at all how to write 523 in the Roman numeral system? I bet you don’t, and I don’t blame you at all.

Still, there are times in our lives (when reading incomprehensible legal documents, for example) when it is mighty useful to be able to read Roman numerals – this Excel tutorial is about how to go about doing this in Excel.

As it so turns out, there is a formula for taking Arabic numerals and converting them into Roman ones. Take a look at this simple list of numbers, for example.

Now, most of us will be able to guess that 1 is I, 5 is V and 17 is… umm, well, XVII? Anything beyond that, and I, at any rate, am way out of my comfort zone.

So what is to be done? A simple formula called (duh!) ROMAN, that’s what is to be done used.

Don’t worry about the zeroin the syntax, it doesn’t really make too much of a difference to anything in our case. In fact, you could safely omit it altogether from the formula.

And oh, in passing, note that 523 is to be written as DXXIII. Might turn out to be useful during Trivia Nights at the local pub.

So that is how you go about converting Arabic numerals to Roman ones.

Which made me wonder (I wonder a lot… a major failing, in the opinion of some people) about a way to get the priginal numbers back. In other words, might it be possbile to enter Roman numerals, and get back Arabic numerals? Turns out there isn’t a formula called ARABIC, sadly.

But hey! That doesn’t mean there isn’t a workaround!

For if writing on this blog and working on Excel has taught me anything, it is that it is always possible to conjure up a workable solution in Excel. Given enough time along with a dash of inspiration and a splash of ingenuity, almost any numerical problem can be solved. And so it proved to be in this case. A couple of quick searches on Google led me to this page, where a rather ingenious solution is provided. I won’t expound upon it here, save to note that it is a very nifty use of three formulas at once (MATCH, ROW and ROMAN).

You get to learn something new everyday, now don’t you?