Excel Challenge #9

[Steve Hales]

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]

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

=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")

 

[Hemant Rupani]

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

there is,

=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)(It was shorter)..... Now, I see, after modification yours better

 

[Muppet]

I don't think this breaks the rules:

VLOOKUP(A1,TABLE,2)

where TABLE is the obvious.

 

[Steve Hales]

I don't think this breaks the rules:

VLOOKUP(A1,TABLE,2)

where TABLE is the obvious.

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.