Worksheet functions using R

The most important worksheet function is RApply

The following formula when entered in a cell

=RApply("sin",A1)

when entered in a cell, will use R to compute the sine of the value in cell A1.

Since Excel worksheet function do not allow named arguments, the above technique cannot be used to run R function calls with named arguments. A simple solution to use named arguments is to include them directly in the function expression:

=RApply("function(x) mean(x, na.rm=TRUE)",A1)

If you need to take the value for a named argument from a worksheet cell (or range), this can be done like in the following example.

=RCall("function(x,y,mytype) plot(x, y, type=mytype)",A1:A10,B1:B10,D1)

In the example above, A1:A10 and B1:B10 should contain the data to be plotted and D1 should contain the string indicating the value for the parameter type.

Only functions returning vectors, matrices, or dataframes can be used in RApply. If a “native” R function returns a more complicated expression, the function used in RApply has to extract a component.

=RApply("function(x) eigen(x)",A1:C3)

will not work

=RApply("function(x) eigen(x)$values",A1:C3)

will work.

All the function expressions used as the first argument of RApply can also contained in a cell and the first argument of RApply can then be a cell reference.

Normally, cell ranges used as arguments to R functions in RApply are transferred as vectors or matrices. The function AsSimpleDF allows to designate a range as dataframe. Here is an example

=RApply("function(mydf) with(mydf,tapply(weight,sex,mean)))",AsSimpleDF(A1:C20))

Personal Tools