Dr. Mark Gardener

Home
About

Providing training for:

  • Ecology
  • Data analysis
  • Statistics
  • R The statistical programming language
  • Data management
  • Data mining

Tips and Tricks - for R and Excel

On this page you can find tips, tricks and hints for using both R and Excel. At the end of each tip there are links forwards and backwards as appropriate. There is also an index of R tips and an index of Excel tips.

For most analytical purposes the combination of Excel and R is unbeatable! Excel is great as a data management tool and for preparing data for analysis. You can also use it to get an overview of your data or to make simple (and not so simple) graphs. R is an analytical "swiss army knife" and can carry out a mind-boggling array of analytical routines as well as producing great graphics.

Tips & Tricks for R | Tips & Tricks for Excel | An Introduction to R | MonogRaphs | Writer's Bloc


Roman numerals

Use =ROMAN to convert regular numbers to Roman numerals.

Use the form parameter to make more concise versions of Roman numerals.

Use =ARABIC to convert Roman numerals to regular numbers.

Top

Roman numerals

You can convert between Roman numerals and regular Arabic numbers using the ROMAN and ARABIC functions.

Regular (Arabic) numbers to Roman numerals

The ROMAN function takes a regular number and gives you the Roman equivalent. There is an additional paramater you can add, to control the appearence of the final Roman number:

=ROMAN(number, form)

The number is simply the number you want to convert (usually you'll give the cell reference). You specify the form as a value from 0–4 or as a logical (TRUE or FALSE) like so:

  • In general the larger the number you use for form the more concise the final Roman numeral becomes.
  • If you use 0 or don't put any value for form you get the classic form.
  • If you specify TRUE you get the classic form.
  • If you specify FALSE you get the most consice form.

The Excel help entry for this function shows an example for 499 to illustrate the effect of altering the form parameter.

=Roman(499, form) Result
0, TRUE or omitted CDXCIX
1 LDVLIV
2 XDIX
3 XDIX
4 or FALSE ID

Roman numerals to regular (Arabic) numbers

Since Excel version 2013 you can convert a number in Roman form to a regular Arabic number; you use the ARABIC function. There is only one parameter, the number you wish to convert (usually you'll give this as a cell reference). It does not matter what form the number is in (see above), it will be evaluated. The function is not case sensitive.

You can specify your number in any number of ways, not strictly in Roman form. As long as you use "allowable" Roman characters the ARABIC function will evaluate the result e.g.

=ARABIC(Xxicd) produces 379 but the reverse, =ROMAN(379) is CCCLXXIX regardless of how concise you are.

The ARABIC function is available In Excel 2013 and Open Office 4.x (and Libre Office) but not in older versions.


Top << Previous Tip: Reversing the axis of an Excel chart >> Next Tip: Named Ranges in Excel
 
Follow me...
Facebook Twitter Google+ Linkedin Amazon
Top Tips & Tricks Home Index of R Tips Index of Excel Tips  
More links:

An introduction to R

See my Publications about Excel, R, statistics and data analysis Courses in R, data analysis, data management and statistics Visit the R Project website
 

See my Publications about statistics and data analysis.

MonogRaphs: random topics in R

Writer's Bloc – my latest writing project includes R scripts

Courses in data analysis, data management and statistics.

My Publications about statistics and data analysis

Managing Data Using Excel, Cover

See my personal pages at GardenersOwn


Top Home
Data Analysis
Contact GardenersOwn Homepage