Excel Challenge #9

Discussion in 'Off-topic' started by Steve Hales, Oct 9, 2015.

  1. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    Got Excel skills? Let's see.

    This one's a classic ;)

    Here are the usual rules.
    • Create a single Excel formula to solve the stated problem.
    • The shortest formula wins (when calculating the length of the formula there's no need to include the leading "=", or the curly braces that Excel uses to represent array formulae).
    • The formula's number format can be set to whatever you want.
    • Conditional formatting is not allowed.
    Problem #9

    Assume that cell A1 contains a positive integer value less than 4,000. Create a single formula to convert the value in A1 to Roman numerals. More specifically, the challenge is to replicate Excel's =ROMAN(A1, 0) function. For example

    A1
    4 --> IV
    19 --> XIX
    49 --> XLIX
    501 --> DI
    1999 --> MCMXCIX
    2015 --> MMXV
     
    Steve Hales, Oct 9, 2015
    #1
  2. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    I'm sure there's some room for improvement here;

    Code:
    =REPT("M",LEFT(RIGHT("000" & A1,4))) & 
CHOOSE(LEFT(RIGHT("000" & A1,3))+1,"","C","CC","CCC","CD","D","DC","DCC","DCCC","CM") & 
CHOOSE(LEFT(RIGHT("000" & A1,2))+1,"","X","XX","XXX","XL","L","LX","LXX","LXXX","XC") & 
CHOOSE(LEFT(RIGHT("000" & A1,1))+1,"","I","II","III","IV","V","VI","VII","VIII","IX")
     
    Steve Hales, Oct 14, 2015
    #2
  3. Hemant Rupani

    Hemant Rupani Senior Member

    there is,
    Code:
    =REPT("M",QUOTIENT(A1,1000))&CHOOSE(LEFT(RIGHT(0&A1,3))+1,"","C","CC","CCC","CD","D","DC","DCC","DCCC","CM")&CHOOSE(LEFT(RIGHT(0&A1,2))+1,"","X","XX","XXX","XL","L","LX","LXX","LXXX","XC")&CHOOSE(RIGHT(A1),"I","II","III","IV","V","VI","VII","VIII","IX")
    I did made my formula just like you but in MOD(QUOTIENT):cool:(It was shorter)..... Now, I see, after modification yours better;)
     
    Hemant Rupani, Oct 14, 2015
    #3
  4. Muppet

    Muppet Member

    I don't think this breaks the rules:

    VLOOKUP(A1,TABLE,2)

    where TABLE is the obvious.
     
    Muppet, Oct 23, 2015
    #4
  5. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    Nice one! I think that almost all problems could be condensed to a solution of this form; the trick is to find the "obvious" TABLE.
     
    Steve Hales, Nov 13, 2015
    #5

Share This Page