I’m sure that this tutorial is going to be an interesting one for you. This time, I’ve included two Google Doc Spreadsheet text functions that related to Roman Numbers. They are ROMAN and ARABIC. You can learn in this tutorial, how to use Roman Numbers in Google Sheets. Also, I am answering your following queries related to Roman numbering in Google Sheets.
1. Auto-generate Roman Numerals as Serial Numbers.
2. How to convert any number from 1 to 3999 into Roman numbers?
3. Find Roman Numerals of any Number from 1 to 3999.
4. How to convert Roman Number to Arabic Number?
To answer all the above questions first I should introduce to you the above said two text functions. Let’s start with how to use Roman Numbers in Google Sheets.
Google Sheets ROMAN Function – Syntax and Examples
In the following syntax, the argument “rule_relaxation” is optional. With rule relaxation, you can shorten the numbering to some extent by applying relaxation to traditional syntax rules. You can apply 0 to 4 numbers as rule relaxation and 0 is the default rule.
ROMAN(number, [rule_relaxation])
Examples:
=ROMAN(99)
XCIX
=ROMAN(99,1)
VCIV
=ROMAN(99,2)
IC
=ROMAN(99,3)
IC
You can refer to this source for more info about rule_relaxation. I think you can normally skip this rule relaxation. In the above examples, you can just go ahead with the formula in red color.
Before going to learn more about the ROMAN function, you should learn one more function related to it and that’s ARABIC. How to use Arabic function in Google Sheets?
Google Sheets ARABIC Function – Syntax and Example
In Google Sheets, you can use the ARABIC function to compute the value of Roman numerals from 1 to 3999.
ARABIC(roman_numeral)
Example:
You can see that there are Roman numerals in cell B3, C3, D3, E3, and F3. All these Roman numbers are equivalent to 299.
The roman numerals are different in these cells due to the different rule relaxations applied. If you refer to any of these cells in the Arabic formula, it would return the value 299.
How to Use ROMAN Numbers in Google Sheets
Now time to go to some practical examples of the use of ROMAN and ARABIC functions.
1. Auto-generate ROMAN Numerals as Serial Numbers
Here I’m going to provide you a custom formula that you can use to generate Roman numbers as serial numbers in Google Sheets.
You can put this formula in any cell and in any column. From that cell, the formula would put serial numbers down up to the number XX (20).
If you wish you can extend the numbering by just changing the reference in the formula.
=ArrayFormula(ROMAN(row(A2:A21)-ROW(A2)+1))
Formula Explanation:
I’ve entered this formula in cell A2 in my sheet. It auto-generates the Roman numbers as serial numbers from I to XX (1 to 20) in my sheet in the range A2: A21. You can copy this formula and paste it in any cell in any column.
Similar:Â Auto Serial Numbering in Google Sheets with Row Function
Suppose you want to generate the auto Roman serial numbering from cell G5, paste the above formula and modify it as below.
=ArrayFormula(ROMAN(row(G5:G21)-ROW(G5)+1))
This formula is to put serial numbers from I to XX. Change the number G21 to G31 to get roman serial numbers from I to XXX (1 to 30).
Other than the function ROMAN, Here I’ve only used ROW and ArrayFormula and it’s a perfect example of how to use Roman Numbers in Google Sheets.
2. How to convert any number from 1 to 3999 to Roman numbers?
I’ve already explained this in the ROMAN function syntax part above. You can convert any number from 1 to 3999 to Roman number using the ROMAN function. Again, for those who may be skipped the above syntax part, here is one example.
=ROMAN(3672)
Result:Â MMMDCLXXII
3. Find Roman Numerals of any Number from 1 to 3999
The above point number two is the answer to this question too. You can just use the ROMAN function to find Roman numerals of any number from 1 to 3999.
4. How to convert Roman Number to Arabic Number?
You can use the ARABIC function to convert any ROMAN number back to Arabic Number. But if you type the formula as below, it won’t work.
=ARABIC(IV)
Instead, the formula would be as below.
=ARABIC("IV")
The Roman numeral should be enclosed by double quotes just like text.
How to Use Roman Numerals in Arithmetic Operations in Google Sheets
The above two functions, ROMAN and ARABIC, are Google Sheets text type functions. The Roman function returns the Roman numerals in text format.
If you want to use it in calculations you should convert it back to the number. Here is one example that shows how to use Roman numbers in calculations in Google Sheets.
The above is an array formula (keyed in cell D4) that involving both ROMAN and ARABIC functions.
In Column A you can see the name of few students’ keyed in, and then their class and division in column B and C respectively.
It’s the status for the year 2017. Now the year is 2018. All the students are promoted to next level.
In column D, I just want to automate the calculation. I just want to add 1 to the class. For example, the student “Leslie” (cell A4) is in class “IV” (cell B4). I want to add 1 to it and put class “V” in cell D4.
The below is the formula that I’ve applied to Cell D4 for this purpose.
=ArrayFormula(roman(arabic(B4:B7)+1))
If it’s not an array formula, it would be like this.
=roman(arabic(B4)+1)
That means first you convert the Roman number in cell B4 to Arabic, then add 1 to it and again convert it back to Roman.
How to Use Roman Numerals in Logical Tests in Google Sheets
Hope you have learned how to use Roman numbers in Google Sheets. In the above example, I’ve detailed how to use it in arithmetical operations. In logical test also you can use Roman numbers.
Here is one example. Please refer the above screenshot to understand the formula references.
=if(B4="IV","In Class 4")
Result: In Class 4
That means you can use Roman numbers as text in a Logical test. But it’s dynamic little bit. See this variant.
=if(B4="IV","In Class "&arabic(B4))
Result: In Class 4
Conclusion
ROMAN and ARABIC functions are more powerful when you use it together. So learn the above examples carefully. That’s all. Enjoy!
