@ is appended to formula at start of cell range

[geebioso]

Hey ycphs,

Thanks for continuing to develop openxlsx!

Expected Behavior

use writeForumla to write and excel formula taking the average of the absolute value of a range of cells. E.g., write "AVERAGE(ABS(A1:A10))" to cell B1.

Actal Behavior

an '@' is appended to the beginning of the cell range. E.g., "AVERAGE(ABS(@A1:A10))"

Steps to Reproduce the Problem

library(openxlsx)

openxlsx::addWorksheet(workbook, "Sheet1")
openxlsx::writeData(workbook, sheet = "Sheet1", 1:10)
openxlsx::writeFormula(workbook, sheet = "Sheet1", startCol = 2, "AVERAGE(ABS(A1:A10))")

openxlsx::saveWorkbook(workbook, file = "test.xlsx", overwrite = TRUE)

The error appears in cell B1. The formula should say "AVERAGE(ABS(A1:A10))", but says
"AVERAGE(ABS(@A1:A10))"

Thanks for your time!

sessionInfo()

R version 3.6.1 (2019-07-05)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: macOS Mojave 10.14.4

Matrix products: default
BLAS:   /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/3.6/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

loaded via a namespace (and not attached):
[1] compiler_3.6.1 tools_3.6.1    packrat_0.5.0 

• Version of openxlsx: 4.1.4
• Version of R: 3.6.1

[JoshuaSturm]

Hi, geebioso.

I'm unable to replicate your issue. When running your code, the output is as expected, with no "@".

library(openxlsx)

workbook <- openxlsx::createWorkbook()
openxlsx::addWorksheet(workbook, "Sheet1")
openxlsx::writeData(workbook, sheet = "Sheet1", 1:10)
openxlsx::writeFormula(workbook, sheet = "Sheet1", startCol = 2, "AVERAGE(ABS(A1:A10))")

openxlsx::saveWorkbook(workbook, file = "formula.xlsx", overwrite = TRUE)

^{Created on 2020-03-13 by the reprex package (v0.3.0)}

Session info

devtools::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value                       
#>  version  R version 3.6.3 (2020-02-29)
#>  os       Ubuntu 19.10                
#>  system   x86_64, linux-gnu           
#>  ui       X11                         
#>  language (EN)                        
#>  collate  en_US.UTF-8                 
#>  ctype    en_US.UTF-8                 
#>  tz       America/New_York            
#>  date     2020-03-13                  
#> 

[geebioso]

Hey JoshuaSturm,

Thanks for checking it out.

Looks like this isn't an openxlsx issue, but a Mac issue. I just tested on PC and formula worked.

And for folks looking to do this on PC: while the formula worked it didn't give the correct result. Excel on PC (I tested 2016) doesn't support array functions like Mac does. It's better to use "=SUMPRODUCT(ABS(A1:A10)) / ROWS(A1:A10)" rather than "AVERAGE(ABS(A1:A10))".

Cheers