When one converts values between different systems, one would expect to be able to convert those values both ways. For example, =CONVERT(10,”mi”,”km”) will tell us that 10 miles equal 16.09 kilometers.
Logically, =CONVERT(10,”km”,”mi”) will convert 10 kilometers into 6.21 miles.
This makes a lot of sense, with the CONVERT function we can convert pretty much anything, from Celsius and Fahrenheit to inches, feet, meters, tablespoons and gallons.
Did you know that there are 768 teaspoons in a gallon? =CONVERT(1,”gal”,”tsp”) knows that.
You can have a look at all the available conversion options here.
There is one thing that can’t go both ways though, Roman numerals.
Although the Roman numerals are not used that often anymore, one can occasionally come across funny looking letters when working out the names of Royalties, Popes, or major sport events. Only the Super Bowl seems to have retained the Roman numerals, while others, such as Olympic Games have switched to the good old decimals.
If you really want to know what the Roman numeral for Superbowl 46 is, try =ROMAN(46,0). The answer is XLVI. The ROMAN function converts any number to a Roman numeral.
And this is where the conversion logic fails. While you can easily convert any number to a Roman numeral in excel, you are on your own if you come across something like this: CCCLXV. (365)
There’s just no formula to convert the Roman numerals to decimals. This just does not make any sense, if you create a way to convert values from one system into another, you really should create a way to convert those values back. Microsoft, I hope you hear me.
The text above contains MDLXXI characters in CCLXVI sentences across IX paragraphs.
The ARABIC function would come in handy here… Right? Wrong, the ARABIC function does not exist 🙁
Ok, I can’t leave you struggling alone here. The credit for creating a custom function to do just that (converting a Roman numeral to a decimal) goes to Andrew Fergus at MrExcel.com. With this custom function you will be able to tackle those Roman numerals and change them to decimals. Good job, Andrew!
