1

I installed BERT (R-language to Excel interface). In the functions.R file that is included, i modified the included Add function to use the na.rm argument, as follows.

Add <- function( ... ) {
  sum(..., na.rm=TRUE);
}

However, it appears that the na.rm arugment is ignored. That is, the Add() function works fine in Excel if all values in the range are present. [that is =R.Add(A1:A5) in Excel works fine if all of cells A1:A5 contain values] But if I delete any value in the range (so the Excel cell is blank), I get #NULL! returned.

Is it possible to utilize the na.rm argument, using BERT so that for R-language functions that have the na.rm argument, it is taken into account and blank cells within the Excel range still compute on the remaining values and do not return #NULL!?

Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
TomCon
  • 13
  • 3

1 Answers1

0

This is a little complicated because the behavior is different if you are passing in one argument (a range) or multiple arguments (individual cells). But in the case of a single argument, if you pass in a range that has empty cells, this will be passed as a list. In that case, you will need to call unlist, e.g.

Add <- function( ... ) {
  sum(unlist(...), na.rm=TRUE);
}

Excel can have ranges that include different types (e.g. strings and numbers), but R can't. So when BERT passes data from Excel to R that has mixed types, it uses a list of lists.

There is a hint about this in the console when it runs, which is a good place to start -- it says that the argument is an invalid type (list).

As I said it's complicated because the three-dots argument could refer to multiple arguments, each of which could be a list or a single value (a scalar), each of which could be different types. In that case you'd need to use one of the apply functions to unlist different arguments. But try the above first.

duncan
  • 446
  • 3
  • 7
  • Thank you. Yes this solves the issue. I mostly posed this question to try to learn from it, in general, how to handle na.rm. It sounds like the handling between R <-> Excel will differ based on the specific R function. – TomCon Feb 14 '18 at 07:16