avoid Excel implicit intersection operator when using openxlsx in R

Question

I am using the openxlsx package in R to make an Excel worksheet which will later have data added to it one row per day.

I am having problems with formulae where I am using ISERROR and STDEV.P, but not with just AVERAGE, MIN, or MAX. Excel is adding an implicit intersection operator (@) to my formulae. I removed my ISERROR code, but I am still having the problem woth STDEV.P

After creating the workbook, wb, and putting headers into it, my code simplified is:

formula1 <- "AVERAGE(E2:E32)"
formula2 <- "AVERAGE(IF(ISERROR(E2:E32),"",E2:E32))"
formula3 <- "STDEV.P(IF(ISERROR(E2:E32),"",E2:E32))"
formula4 <- "STDEV.P(E2:E34)"
writeFormula(wb, 1, x=formula1, startCol = 5, startRow = 34)
writeFormula(wb, 1, x=formula2, startCol = 5, startRow = 35)
writeFormula(wb, 1, x=formula3, startCol = 5, startRow = 36)
writeFormula(wb, 1, x=formula4, startCol = 5, startRow = 37)
writeFormula(wb, 1, x=formula5, startCol = 5, startRow = 38)
saveWorkbook(wb, file = "Log.xlsx", overwrite = TRUE)

This is what Excel does to my formula:

formula1
What in R is the string:  AVERAGE(E2:E32)
What Excel shows:        =AVERAGE(E2:E32)             ok; works

formula2
What in R is the string:  AVERAGE(IF(ISERROR(E2:E32),"",E2:E32))
What Excel shows:        =AVERAGE(IF(ISERROR(@E2:E32),"",E2:E32))    value error in Excel

formula3
What in R is the string:  STDEV.P(IF(ISERROR(E2:E32),"",E2:E32))
What Excel shows:        =@STDEV.P(IF(ISERROR(@E2:E32),"",E2:E32))   name error in Excel

formula4
What in R is the string:  STDEV.P(E2:E34)
What Excel shows:        =@STDEV.P(E2:E34)               Name error in Excel

Each formula with the @ added does NOT work in Excel, whether or not I have data in E2:E34. I am getting a ?NAME error for formulae using STDEV.P and value errors for other ones.

Does anyone know how to fix this? Is there something I can add to my R code to force Excel to stop adding these @? Is there some way to change my formula strings in R so that Excel will not give an error?

Thank you for any help you can provide.

Answer 1

I can't replicate the Excel error to show @ in the Excel formula but the reason why you get the #VALUE with the formula AVERAGE(IF(ISERROR(E2:E32),"",E2:E32)) is because ISERROR requires a cell input rather than the range. So if you change this to AVERAGE(IF(ISERROR(E2),"",E2:E32)) then this should work.

Also make sure to replace the double quotes " in the beginning and at the end with a single '. This is required because in your Excel formula you are using " and R would think this is the end of the string ".

If you are using an Excel function that includes . then your formula should begin with _xlfn. so to use STDEV.P this should be _xlfn.STDEV.P(E2:E34).

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "1")

# This works
formula1 <- "AVERAGE(E2:E32)"

# This won't work (note it begins with a single ')
formula2 <- 'AVERAGE(IF(ISERROR(E2:E32),"",E2:E32))'

# This should work
formula3 <- 'AVERAGE(IF(ISERROR(E2),"",E2:E32))'

# This won't work
formula4 <- 'STDEV.P(IF(ISERROR(E2:E32),"",E2:E32))'
formula5 <- "STDEV.P(E2:E34)"

# This should work. For a Excel functions with . start with _xlfn
formula6 <- "_xlfn.STDEV.P(E2:E34)"
    
writeData(wb, 1, 1:30, startRow = 2, startCol = 5)
writeFormula(wb, 1, x=formula1, startCol = 5, startRow = 34)
writeFormula(wb, 1, x=formula2, startCol = 5, startRow = 35)
writeFormula(wb, 1, x=formula3, startCol = 5, startRow = 36)
writeFormula(wb, 1, x=formula4, startCol = 5, startRow = 37)
writeFormula(wb, 1, x=formula5, startCol = 5, startRow = 38)
writeFormula(wb, 1, x=formula6, startCol = 5, startRow = 39)
saveWorkbook(wb, file = "Log.xlsx", overwrite = TRUE)