How to Use Roman Numbers in Google Sheets

Published on

In this tutorial, you’ll learn how to use Roman numerals in Google Sheets. Additionally, I’ll answer the following questions related to Roman numbering in Google Sheets:

  1. How to auto-generate Roman numerals as serial numbers.
  2. How to convert any number from 1 to 3999 into Roman numerals using the ROMAN function.
  3. How to convert Roman numerals back to Arabic numbers using the ARABIC function.

To answer all of these questions, I’ll first introduce the two functions mentioned above. Let’s begin with how to use Roman numerals in Google Sheets.

ROMAN Function – Syntax and Examples

In the following syntax, the argument rule_relaxation is optional. With rule relaxation, you can shorten the representation of Roman numerals to some extent by applying relaxed rules to the traditional syntax. You can specify values from 0 to 4 for rule relaxation, where 0 is the default and represents the correct and traditional way to write Roman numerals.

Syntax:

ROMAN(number, [rule_relaxation])

Examples:

  • =ROMAN(499) // returns CDXCIX
  • =ROMAN(499, 1) // returns LDVLIV
  • =ROMAN(499, 2) // returns XDIX
  • =ROMAN(499, 3) // returns VDIV
  • =ROMAN(499, 4) // returns ID

Regarding rule relaxation, let’s consider the first two Roman numerals, “CDXCIX” and “ID”:

CDXCIX:

  • CD: 500 – 100 = 400
  • XC: 100 – 10 = 90
  • IX: 10 – 1 = 9

So, CDXCIX = 400 + 90 + 9 = 499.

ID:

  • 500 – 1 = 499.

Please note that the ROMAN function is capable of formatting numbers from 1 to 3999, inclusive.

For more information on rule relaxation, please check out this source: Official Documentation

ARABIC Function – Syntax and Examples

In Google Sheets, you can use the ARABIC function to compute the value of Roman numerals ranging from 1 to 3999.

Syntax:

ARABIC(roman_numeral)

Example:

The following formulas will return 499, even though the Roman numerals are in a rule-relaxed format:

  • =ARABIC("CDXCIX")
  • =ARABIC("LDVLIV")
  • =ARABIC("XDIX")
  • =ARABIC("VDIV")
  • =ARABIC("ID")

Now it’s time to explore some practical examples of using the ROMAN and ARABIC functions.

Auto-generate ROMAN Numerals as Serial Numbers

Want to use Roman numerals as serial numbers in a column or row? You can use SEQUENCE within ROMAN and enter the formula as an array formula.

The following formula will return the Roman numerals from 1 to 100 in a column:

=ArrayFormula(ROMAN(SEQUENCE(100)))
Using Roman numerals as serial numbers in Google Sheets

If you want them in a row, use this formula:

=ArrayFormula(ROMAN(SEQUENCE(1, 100)))

You can replace 100 with any number between 1 and 3999 in the above formulas to generate Roman serial numbers accordingly.

How to Convert Roman Numerals to Arabic Numbers

You can use the ARABIC function to convert any Roman numeral back to an Arabic number. However, if you type the formula like this, it won’t work:

=ARABIC(IV)

Instead, the correct formula would be:

=ARABIC("IV")

The Roman numeral should be enclosed in double quotes, just like text. Alternatively, you can enter the Roman numeral in a cell and reference that cell in the ARABIC function.

How to Use Roman Numerals in Arithmetic Operations in Google Sheets

Assume you have the Roman numeral IV and want to add I to it to get V. How do you do that?

To use Roman numerals in calculations, you should convert them to Arabic numerals:

=ROMAN(ARABIC("IV") + ARABIC("I"))

The above formula will add IV + I and return V.

Error Handling

The ROMAN function will return the #VALUE! error when the specified number is not between 1 and 3999. You can wrap the formula with IFERROR to handle this error.

If you want to inform the user of the specific reason for the error, you can apply a logical test as follows:

=IF(ISBETWEEN(A1, 1, 3999), ROMAN(A1), IF(ISTEXT(A1), "Invalid Input: Text Not Supported", IF(OR(A1<1, A1>3999), "Error: Number should be between 1 and 3999",)))

The ARABIC function will return a similar error if the specified value is not a valid Roman numeral or if it is text. To remove that error, simply wrap it with IFERROR.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.