R and Calc Documentation

Please note that this documentation is for Version 0.1.1. See the main R and Calc page for information on installation and setup.

Cell Functions

Note that the function names may change in future versions... I'm not really concerned about properly naming everything for users yet, but making things simpler for development.

• RNDBLMAT( <command> , <matrix> , <matrix>): passes a command and two matrices. For example, RNDBLMAT("cov";A1:A100;B1:B100).
• RNMAT( <command> , <matrix> ): passes a command and matrix as a parameter. For example, RNNUM("var";A1:A100).
• RNNUM( <command> , <number> ): passes a command and a numeric parameter. For example, RNNUM("rnorm"; 1).
• RNUMPROP( <command> , <property> ): passes a command to R and prints a specific property from the output. This is useful if the values returned from R aren't numeric but contain a variety of information. For example, RNUMPROP("cor.test(c(1,2,3,4), c(1,2,2,3))"; "p.value").
• RSIMPLENUM( <command> ): passes a command to R, prints a number if one is returned.
• RTESTCONNECTION(): tests R connection (for RServe).

R Add-On Menu

• RDUMP(): creates a new spreadsheet with the object structure returned by a specific R script.
• Advanced > Load External Script: allows you to load an external GUI script for custom R commands.

Tips & Tricks

Running External Libraries

At this time, it's not possible to load libraries ahead of time using Rserve. However, you can load a library through a function cell. The example below loads a library called sna and calls a function, passing a matrix as a parameter. Note that the library needs to be installed prior to using it.

RNMAT("library(sna);gden"; A1:D4)

summary() Hack for Complex R Objects

There are times when the R object structure you are dealing with is very complex, to the point that it's difficult to find specific values. One such example is the nonlinear least squares function (used for curve fitting, for example). You use this function by calling nls() and one of the most useful things it gives is values for coefficients in an equation. The only problem is the object returned by nls() is organized in a way that makes it difficult to find values for these coefficients.

One workaround is to call nls() with summary()... This will allow you to see some of the variables (such as coefficient values) and makes it much easier to extract information using the R/Calc interface.

See the example implementation with one variable: http://www.11-55.org/rcalc/2007-06-01/rcall_nls.txt

Creating Your Own Dialogs and Scripts

Using a basic text file, it is possible to create your own scripts and functions that will then call R and provide output. A basic correlation example is provided, and an outline of the tile structure is given below.

First and foremost, there are four sections to a file:

• __ETC: ignored -- a set of comments.
• __GUI: for user interface objects.
• __CALL: allows you to define the R function call based on GUI inputs.
• __OUTPUT: allows you to set where the output will go.

Using __GUI:

Currently, the following GUI objects can be defined in the file:

• TextField: for text-based arguments.
• NumberField: for numeric arguments -- either actual numbers or a reference to a sheet cell.
• ArrayField: refers to data in the spreadsheet (current active sheet).
• Label: a basic label.
• RunButton: runs the code and prints output.
• CancelButton: closes the dialog.
• Dialog: allows you to define the size and location of the dialog.
• OutputField: allows for the user to input information for outputs.
• ComboBox: creates a combo box. Rather than passing label_text, pass a list of options with ";" as the delimiter. For example, "a;b;c;d" will have the combo box show four options: a, b, c, and d.

The format for the above is as follows, with italic fields representing variables and parameters: Object_Name<-Label( x position , y position , width , height, label_text ). If you want to use special symbols in labels or other parts of the GUI, the following characters need to be replaced with the corresponding variables:

• ( {$BL}
• ) {$BR}
• = {$EQ}

Using __CALL:

In this case, simply write your call text, with variables being represented as {$variable-name} where the name itself corresponds to the names you gave the GUI objects above. This only works with TextField, NumberField, and ArrayField. You can have multiple lines of R scripts here, but can only use the output of the final line of code in this section.

Using __OUTPUT:

In this case, you write every line like so: Cell-Address<-Value where Value is either the name of a specific property in the R object structure (e.g. p.value or estimate) or a general piece of text. The software will always test to see if your text is a property, and if it is not, the text itself will be printed.