Trace: • excel_worksheet_functions_using_r

# 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))