Home » Convert Roman to Decimal

Convert Roman to Decimal

Convert from Roman to Decimal. For conversion from Roman to Decimal Number, value of characters to be used – M-1000, D-500, C-100, L-50, X-10, V-5 & I-1. Example – for MCLXVI 1000 + 100 + 50 + 10 + 5 + 1 = 1166

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 154
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Convert Roman to Decimal with Power Query

Power Query solution 1 for Convert Roman to Decimal, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Arabic = Table.TransformRows(
    Source, 
    each 
      let
        b = List.ReplaceMatchingItems(
          Text.ToList([Roman]), 
          List.Zip({{"I", "V", "X", "L", "C", "D", "M"}, {1, 5, 10, 50, 100, 500, 1000}})
        )
      in
        List.Sum(
          List.Transform(
            List.Positions(b), 
            (n) => if b{n} < b{n + 1}? ?? false then - b{n} else b{n}
          )
        )
  )
in
  Arabic
Power Query solution 2 for Convert Roman to Decimal, proposed by Zoran Milokanović: 
let
 Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
 fxR2D = (I as text) => 
 let
 Result = 
 if Text.StartsWith(I, "M") then [D = 1000, R = Text.Range(I, 1)]
 else if Text.StartsWith(I, "CM") then [D = 900, R = Text.Range(I, 2)]
 else if Text.StartsWith(I, "D") then [D = 500, R = Text.Range(I, 1)]
 else if Text.StartsWith(I, "CD") then [D = 400, R = Text.Range(I, 2)]
 else if Text.StartsWith(I, "C") then [D = 100, R = Text.Range(I, 1)] 
 else if Text.StartsWith(I, "XC") then [D = 90, R = Text.Range(I, 2)]
 else if Text.StartsWith(I, "L") then [D = 50, R = Text.Range(I, 1)]
 else if Text.StartsWith(I, "XL") then [D = 40, R = Text.Range(I, 2)]
 else if Text.StartsWith(I, "X") then [D = 10, R = Text.Range(I, 1)]
 else if Text.StartsWith(I, "IX") then [D = 9, R = Text.Range(I, 2)]
 else if Text.StartsWith(I, "V") then [D = 5, R = Text.Range(I, 1)]
 else if Text.StartsWith(I, "IV") then [D = 4, R = Text.Range(I, 2)]
 else if Text.StartsWith(I, "I") then [D = 1, R = Text.Range(I, 1)]
 else [D = 0, R = I]
 in
 Result,
Power Query solution 3 for Convert Roman to Decimal, proposed by Zoran Milokanović: 
2/2
 AddNumber = Table.AddColumn(Source, "Number", each List.Sum(List.Generate(
 () => fxR2D([Roman]),
 each [D] > 0,
 each fxR2D([R]),
 each [D]
 ))),
in
 Solution
Power Query solution 4 for Convert Roman to Decimal, proposed by Sergei Baklan: 
let
  Source = Excel.CurrentWorkbook(){[Name = "roman"]}[Content], 
  tbl = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), 
  roman = {
    {1, "I"}, 
    {4, "IV"}, 
    {5, "V"}, 
    {9, "IX"}, 
    {10, "X"}, 
    {40, "XL"}, 
    {50, "L"}, 
    {90, "XC"}, 
    {100, "C"}, 
    {400, "CD"}, 
    {500, "D"}, 
    {900, "CM"}, 
    {1000, "M"}
  }, 
  fnArabic = (txt as text, n as number) =>
    let
      nextRoman = List.Last(
        List.RemoveNulls(
          List.Transform(roman, (x) => if Text.StartsWith(txt, x{1}) then x else null)
        )
      ), 
      newNumber = n + nextRoman{0}, 
      cutRoman = Text.RemoveRange(txt, 0, Text.Length(nextRoman{1}))
    in
      if cutRoman = "" then newNumber else @fnArabic(cutRoman, newNumber), 
  addNumbers = Table.AddColumn(tbl, "Number", each fnArabic([Roman], 0))
in
  addNumbers
Power Query solution 5 for Convert Roman to Decimal, proposed by Udit Chatterjee: 
let
  fxRomanToDecimal = (romanNum as text) =>
    let
      romanNumList = Text.ToList(romanNum), 
      decimalNumList = List.Transform(
        romanNumList, 
        each 
          if _ = "M" then
            1000
          else if _ = "D" then
            500
          else if _ = "C" then
            100
          else if _ = "L" then
            50
          else if _ = "X" then
            10
          else if _ = "V" then
            5
          else if _ = "I" then
            1
          else
            0
      ), 
      numPositions = List.Positions(decimalNumList), 
      decimalListVal = List.Transform(
        numPositions, 
        each try
          
            if decimalNumList{_} < decimalNumList{_ + 1} then
              - decimalNumList{_}
            else
              decimalNumList{_}
        otherwise
          decimalNumList{_}
      ), 
      decimalNum = List.Sum(decimalListVal)
    in
      decimalNum, 
  Source = xlProblem154, 
  addFunctionCol = Table.AddColumn(Source, "Binary", each fxRomanToDecimal([Roman]), Int64.Type)
in
  addFunctionCol

Solving the challenge of Convert Roman to Decimal with Excel

Excel solution 1 for Convert Roman to Decimal, proposed by Bo Rydobon 🇹🇭: 
=ARABIC(A2:A11)
=MAP(A2:A11,LAMBDA(a,LET(b,XLOOKUP(MID(a,SEQUENCE(LEN(a)),1),{"I";"V";"X";"L";"C";"D";"M"},{1;5;10;50;100;500;1000},0),
SUM(b-b*2*IFNA(b
Excel solution 2 for Convert Roman to Decimal, proposed by John V.: 
=ARABIC(A2:A11)

But, the "manual way" could be:
✅ =MAP(A2:A11,LAMBDA(x,LET(n,XLOOKUP(MID(x,SEQUENCE(LEN(x)),1),MID("MDCLXVI",ROW(1:7),1),{1000;500;100;50;10;5;1}),SUM(n*-1^IFNA(DROP(n,1)>n,)))))
Excel solution 3 for Convert Roman to Decimal, proposed by Kris Jaganah: 
=ARABIC(A2:A11)
Excel solution 4 for Convert Roman to Decimal, proposed by Kris Jaganah: 
=MAP(A2:A11,LAMBDA(x,LET(a,MID(x,SEQUENCE(LEN(x)),1),b,{"M";"D";"C";"X";"V";"I";"L"},c,{"1000";"500";"100";"10";"5";"1";"50"},d,--XLOOKUP(a,b,c),e,VSTACK(DROP(d,1),TAKE(d,-1)),SUM(IF(d
Excel solution 5 for Convert Roman to Decimal, proposed by Julian Poeltl: 
=ARABIC(A2:A11)
Excel solution 6 for Convert Roman to Decimal, proposed by Aditya Kumar Darak 🇮🇳: 
=ARABIC(
    A2:A11
)
Excel solution 7 for Convert Roman to Decimal, proposed by Timothée BLIOT: 
=LET(A,A2:A11, R, {"I","V","X","L","C","D","M"}, N, {1,5,10,50,100,500,1000},
B, TEXTSPLIT(TEXTJOIN("/",,BYROW(A, LAMBDA(x, TEXTJOIN(":",,MID(x,SEQUENCE(LEN(x)),1))))),":","/",,,""),
C, MAP(B, LAMBDA(x, XLOOKUP(x,R,N,0) )),
D, MAKEARRAY(ROWS(B),COLUMNS(B), LAMBDA(x,y, IF(yINDEX(C,x,y),-INDEX(C,x,y),INDEX(C,x,y)),
INDEX(C,x,y)) )), BYROW(D, LAMBDA(x, SUM(x))))
Excel solution 8 for Convert Roman to Decimal, proposed by Oscar Mendez Roca Farell: 
=MAP(A2:A11,LAMBDA(a, LET(_c, IFERROR(FIND(MID(a, SEQUENCE(LEN(a)), 1),"IVXLCDM"), ),_f, LAMBDA(i,(MOD(i-1,2)+(-1)^(i=0))*10^(INT((i-2)/3+(i>2))+(i<2))),_r,5*_f(_c-1),_s,(-1)^(IFNA(_r-DROP(_r,1),)<0),SUMPRODUCT(IFERROR((1/_r)^-1,1),_s))))
Excel solution 9 for Convert Roman to Decimal, proposed by Sunny Baggu: 
=MAP(A2:A11,LAMBDA(a,
LET(_m,MID(a,SEQUENCE(LEN(a)),1),
_word,"M-1000, D-500, C-100, L-50, X-10, V-5, I-1",
_vtbl,TEXTSPLIT(TRIM(_word),"-",", "),
_val,XLOOKUP(_m,TAKE(_vtbl,,1),TAKE(_vtbl,,-1))+0,
_e1,LAMBDA(x,CHOOSEROWS(_val,x)),
SUM(VSTACK(DROP(SCAN(0,SEQUENCE(ROWS(_val)),LAMBDA(a,v,IF(_e1(v)>=_e1(v+1),_e1(v),-_e1(v)))),-1,),TAKE(_val,-1,1))))))
Excel solution 10 for Convert Roman to Decimal, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=MAP(A2:A11;LAMBDA(x;SUM(XLOOKUP(MID(x;SEQUENCE(LEN(x));1);{"M";"D";"C";"L";"X";"V";"I"};VSTACK(1000;500;100;50;10;5;1)))))
Excel solution 11 for Convert Roman to Decimal, proposed by Guillermo Arroyo: 
=ARABIC(A2:A11)
=LET(r,{"M";"D";"C";"L";"X";"V";"I"},n,{1000;500;100;50;10;5;1},MAP(A2:A11,LAMBDA(m,LET(x,XLOOKUP(MID(m,SEQUENCE(,LEN(m)),1),r,n),y,HSTACK(IF(DROP(x,,-1)
Excel solution 12 for Convert Roman to Decimal, proposed by Mohamed Helmy: 
=MAP(A2:A11,LAMBDA(a,LET(
v,LOOKUP(MID(a,SEQUENCE(LEN(a)),1),{"c","d","i","l","m","v","x"},{100,500,1,50,1000,5,10}),
r,DROP(v,-1),
SUM(IF(DROP(v,1)>r,-r*2),v))))
Excel solution 13 for Convert Roman to Decimal, proposed by Gabriel Raigosa: 
= 7999

=NUMERO.ARABE(A2:A11)

 =ARABIC(A2:A11) 

 =VSTACK({"Roman","Number"},LET(Dat,A2:A11,HSTACK(Dat,ARABIC(Dat))))
Excel solution 14 for Convert Roman to Decimal, proposed by Gabriel Raigosa: 
=MAP(A2:A11,LAMBDA(x,LET(n,XLOOKUP(MID(x,SEQUENCE(LEN(x)),1),MID("MDCLXVI",ROW(1:7),1),{1000;500;100;50;10;5;1}),SUM(n*-1^IFNA(DROP(n,1)>n,)))))
Excel solution 15 for Convert Roman to Decimal, proposed by Daniel Madhadha: 
=ARABIC(
    A2:A11
)

Solving the challenge of Convert Roman to Decimal with Python in Excel

Python in Excel solution 1 for Convert Roman to Decimal, proposed by Alejandro Campos: 
Last number is 7999 and not 8999
def roman_to_decimal(roman):
 roman_values = {
 'I': 1,
 'V': 5,
 'X': 10,
 'L': 50,
 'C': 100,
 'D': 500,
 'M': 1000
 }
 
 total = 0
 prev_value = 0
 
 for char in reversed(roman):
 value = roman_values[char]
 if value < prev_value:
 total -= value
 else:
 total += value
 prev_value = value
 
 return total
roman_numerals = xl("A2:A11")[0]
decimal_values = [roman_to_decimal(roman) for roman in roman_numerals]
decimal_values

&&&

Leave a Reply