Cactus: Roman nvmerals.

Hyde, Hartley

Regular visitors to the AAMT website at www.aamt.edu.au will have noticed that our website carers have been very busy. The front page has a new and very "noughties" style which is both functional and attractive. Much of the content has been reorganised into more logical groupings to make things easier to find, and a new navigation system also adds to the clarity of the content of sub-folders. The site also includes the now common ability to share links to pages through social networking sites.

When I looked at the new website, I was encouraged to explore and I found some links that, I am assured, have been there all the time.

Some of the links I found took me to sister sites in the UK and New Zealand. Obviously I was keen to explore the resources that they are offering. However, when I launched many of their spreadsheets I found that they are black-boxes. Data can be entered in a prominent cell and an answer appears, as if by magic, in another prominent cell and there is no apparent connection between the two. In the example shown below, a decimal numeral is entered into the lower cell and a conversion to Roman numerals appears in the higher cell between the two pictures that appear somewhat more Greek than Roman.

[ILLUSTRATION OMITTED]

Sometimes the hidden processes use macro scripting and at other times there are hidden rows and columns of cells containing regular spreadsheet formulas. While such spreadsheets provide a quick answer, and are often fun to use, I believe that students learn very little from this approach. All of the processes leading from input to answer should be logically presented so that students can see clearly how the answer has been reached. This exercise can also be used to teach the simpler text-string functions.

In this article I have reworked the conversion from decimal to Roman and back using what I believe to be more instructive spreadsheets. I have taken an upper limit of 3999 because if we want to write 4000 we have to use MMMM or resort to superior bars.

Converting a decimal numeral to a Roman numeral

Start by widening column C and giving rows 2 and 9 more depth. This creates large spaces at cells C2 and C9 to hold the input numeral and the answer. I have chosen 3408 and placed it at C2 as my initial test numeral, as shown below.

[ILLUSTRATION OMITTED]

In column C the component digits of the input numeral are separated. This is done in two steps. At B4 the input numeral is divided by 10 and then the decimal is trimmed off using the integer function to give 340.

B4: =INT(C2/10) followed by B5: =INT(B4/10) and B6: =INT(B5/10)

and then in column C ten times these numbers are subtracted from the previous result.

C4: =C2-10*B4 C5: =B4-10*B5 C6: =B5-10*B6 and C7: =B6

In the cells F4:J13 we build a lookup table. Column F has a list of decimal digits and in subsequent columns is the Roman equivalent for each power of 10. Thus the formula D4:=VLOOKUP(C4,F$4:J$13),2) takes the 8 from cell C4 and looks down column F until it finds the 8 at F12 and then moves to column 2 of the table (i.e. column G) where it finds the Roman grouping VIII at cell G12 which is copied to cell D4 next to the 8.

This formula is copied down to cells D5, D6 and D7. The only modification then required is to change the column looked up in the table thus:

D5: = VLOOKUP(C5,F$4:J$13,3)

D6: = VLOOKUP(C6,F$4:J$13,4)

D7: = VLOOKUP(C7,F$4:J$13,5)

This process successfully copies the Roman grouping CD (i.e. 400) to cell D6 next to the 4 and the grouping MMM (i.e. 3000) to cell D7 next to the three. However, there are problems when we try to copy the blank space from H4 to D5 next to the zero. There is limited success if we go to cells G4:J4 and press the space bar at each cell. However, this will copy the space to cell D5 and the final answer at C9 appears as MMMCD VIII which is not what we want.

Since Excel recognises the space character at cell G4 as a text-string, all we need to do is reduce the length of this string to zero using:

G4: = LEFT(,0) and copy this to G4:GJ.

Finally, we join the strings of Roman groupings together using the concatenate formula thus:

C9: =CONCATENATE(D7,D6,D5,D4) which should give us MMMCDVIII.

Unfortunately, more sophisticated spreadsheets such as Excel constantly complain because the formulae refer to cells which are empty or only contain a space. We know why, so simply choose to ignore each complaint as shown below-right.

Numerals larger than MMMCMXCIX

Considering that the largest Roman numerals used today are chiselled into foundation stones and headstones to represent a year, the limitation of less than 4000 will be adequate for quite a while yet. However, modification of the spreadsheet to allow larger numerals is possible.

Multiplication by 1000 was achieved by adding a horizontal bar above a numeral. Ifrah (1998) claims that, while some modern authors denote multiplication by a million using two horizontal bars, "no evidence of this in current Roman inscriptions has been found."

During the imperial period, multiplication by 100 000 was denoted by enclosing part of the numeral with three sides of a box above and at each side. In this way the numeral 165 178 316 could be written

1651 x 100 000 + 78 x 1000 + 316

MDCLI | LXXVIII CCCXVI

Such notations did lead to serious errors. According to Seneca, Livia, mother of the Emperor Tiberius had to pay large sums of money to her legates. Livia had written a legacy of 50 000 000 sesterces to Galba in the form

[ILLUSTRATION OMITTED]

When Livia eventually died, Tiberias was the principal heir. Never known for his generosity, he chose to interpret the curve above the CCCCC as a line rather than as three sides of a box. Galba therefore received only 500 000 sesterces.

The Romans did not have a specific word for a million. They would have written a million as decies centena milia: ten hundred thousand.

[ILLUSTRATION OMITTED]

Converting a Roman numeral to a decimal numeral

To keep the spreadsheet manageable, the input is presumed to be less than 4000. In this case (as shown below) we enter a test numeral of MMMCMXCIV into the cell C2. The maximum length the string of Roman characters would occur if we processed the numeral

MMMDCCCLXXXVIII (i.e., 3888).

List the numerals 1 to 15 in the column B4:B18. This can be done quickly by typing 1 and 2 and then selecting both reveals a small square in the lower-right corner that can be pulled down to the cell B18.

In the next column we isolate the individual characters comprising the Roman numeral. Therefore next to the 1 in B4 we find the first character of the number using

C4: = MID(C$2,B4,1) and then the second

C5: = MID(C$2,B5,1) and copy down to C18.

C6: = MID(C$2,B6,1)

C7: = MID(C$2,B7,1) etc.

At C7 the C$2 points us to the input string MMMCMXCIV. The formula then looks at cell B7, finds the numeral 4 and looks along the string and returns the coloured string starting at the fourth character. The formula then looks at the 1 and returns just the first character 'C' of that string. In this way the whole string of Roman characters is copied individually into column C as shown below.

[ILLUSTRATION OMITTED]

In the space D4:G18 there is a table of values. In most cases we simply copy the face value of whichever Roman character appears in column C. Next to the first three Ms appears the value 1000. Next to each C appears the value 100, etc. However, when we come to the fourth M, it is preceded by a C which means that 100 must be subtracted from the 1000. Since the C already has 100 next to it, it is easier to check for the presence of the C and if a C precedes the M then that particular M is valued at only 800. This is achieved by using the formula

D5: =IF(C5="M"),(IF((C4="C"),800,1000)),0) and copy this down to D18.

Clearly the formulas in column D process only Ms and Cs and otherwise return a zero. The formula at D4 is modified because a 'C' cannot appear at D3. Thus

D4: = IF((C4="M"),1000,0)

In column E we process the Cs and Ds but must check to see if a D is preceded by a C or whether a C is preceded by an X thus:

E5: =IF((C5="D"),IF((C4="C"),300,500),0) +IF((C5="C"),IF((C4="X"),80,100),0)

The formula at E4 can also be simplified to:

E4: =IF((C4="D"),500,0) + IF((C4+"C"),100,0)

The formulae in column F are similar to those in column E.

F5: =IF((C5="L"),IF((C4="X"),30,50),0) +IF((C5="X"),IF((C4="I"),8,10),0)

The formula at F4 can also be simplified to:

F4: =IF((C4="L"),50,0) + IF((C4+"X"),10,0) mn

The formulae in column G are similar to those in column E.

G5: =IF((C5="V"),IF((C4="I"),3,5),0) +IF((C5="I"),1,0)

The formula at G4 can also be simplified to

G4: =IF((C4="V"),5,0) + IF((C4+"I"),1,0)

Finally we total the numbers in each column using D19: = SUM(D4:D18) etc. and then the answer at C20 is given by

C20: =SUM(D19:G19)

Even if students cannot work their way through multiple IF formulae, the pattern of numbers shown in the table shows them the logic used to calculate the answer. This must be more satisfying than using a spreadsheet that gives only an answer.

Reference

Ifrah, G. (1998). The universal history of numbers. London: Harvill.

Hartley Hyde

cactus.pages@internode.on.net