Types: Arguments and Return Values
Some of this is still subject to change; this reflects what's in the current source/release.
Calling R functions from Excel, and returning values to Excel, requires some conversion between different type systems. Generally this is transparent to the user, but there are some special cases which need attention.
We don't support complex numbers. Excel sort of supports them, but not really -- they are actually Strings. Thus it's impossible to tell the difference between a string and a complex number. We could guess (and it might not be that expensive), but for now we are not doing that.
When calling R functions from Excel, values are passed as the type Excel thinks they are. Usually this means numeric values are Reals, although in some cases these may be Integers. Excel supports Integer, Real (double), Logical (boolean), and String.
- Single values
Single value arguments are passed as scalars of the appropriate type
- Ranges (and Excel Arrays)
If you pass an Excel range as an argument (e.g. =R.SUM(A1:B2)) it will be passed to R as a matrix. If all the cells in the range contain numeric values, this will be a Real matrix. If there are mixed types, including empty cells, it will be passed as a Generic matrix.
If your Excel range has headers, BERT won't automatically recognize this. If you want to pass in data with row or column headers, your function will need to convert them from the first row/column of the matrix.
- Three Dots (...) Arguments
You can use ... arguments in your functions, although only 16 arguments (total) will be accepted. Argument names are used in the Excel function helper box, so to prevent confusion we suggest only using ... as the last argument.
- Function (Tilde) Arguments
We don't support function arguments (e.g. Y ~ X ). There's no meaningful way to represent a function argument in Excel. If you want to use function arguments, we suggest using separate arguments and then constructing the function in your R code.
As with arguments, most types can pass directly as return values.
- Single values
Scalar values returned from functions are set as the appropriate type.
- Vectors, Lists, and Matrices
Vectors, lists, and matrices are returned as the Excel Array type. Conceptually this is similar to a generic matrix in R. See the note on working with Excel Arrays below. Row and column names are ignored for these types - to use row or column names, return a data frame.
- Data Frames
Data frames are handled specially to preserve row and column names. If your function returns a data frame, this will be treated as an Excel Array with an additional row and column for the header names.
Excel Arrays are an internal type that refer to multiple values. Conceptually these are similar to generic matrices in R. There are two general ways to use Arrays in Excel: inserting them into a range of cells, or passing them into functions.
To insert an Array into a spreadsheet:
- Select a range of cells
- Type your function in the Excel function bar
- Press
Ctrl+Shift+Return
Once you've entered an Array formula, it will appear with braces in the Excel function bar.
If your Array is larger than the selected range, data will be omitted. If your Array is smaller than the selected range, you will see #N/A errors. You can resize the range, but it's not easy.
To resize an Array formula:
- Select the full range of cells. You can also select one cell in the range and press
Ctrl+/. - Click in the function bar and press
Ctrl+Enter. - You can now resize the range using the gripper (the square at the corner of the selection).
- Once you have resized the range, click in the function bar and press
Ctrl+Shift+Enter.
Using Arrays as function arguments:
Some (but not all) Excel functions can take Arrays as arguments. For example,
=SUM( R.Identity( 4 ))
Where R.Identity is a function from our sample functions.R file that returns the identity matrix (via R's diag function).
Many people don't know that Excel includes a number of matrix functions including math operations, transposition, and inversion. These all take (and return) Excel Arrays. In the Insert Function Dialog, matrix operations are in the Math and Trig section. Functions include MINVERSE, MUNIT, MMULT, MDETERM, and TRANSPOSE.